Re: how to query the structure of a database

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 28 Jan 2002 13:05:42 -0800
Message-ID: <6dae7e65.0201281305.4353fab4_at_posting.google.com>


"Massimo Fuccillo" <mfuccillonospam_at_tiscalinetnospam.it> wrote in message news:<D8958.18173$6e5.625842_at_twister2.libero.it>...
> Hi all
>
> Is there a standard way (using SQL) to query the structure of a database?
>
> In other words:
> one or more query to obtain the names of the non system tables,
> one or more query, for each table, to obtain field names, field types, field
> sizes
> one or more query for the constraints, indexes etc.
> independently from the database provider (Oracle, Db2, Sqlserver, etc)
>
> Thanks in advance for any suggestion.
>
> Regards
>
> Massimo Fuccillo

I have no experience of other dbs than db2, but Im pretty sure that other wendors have chosen different names for the system tables. As for db2 you can use tables like:

sysibm.systables, sysibm.syscolumns, sysibm.sysindexes etc.

For a generic solution you might be able to use a script and let variables be dependent of the chosen db as in:

#!/bin/sh

db=$1

if [ "X$db" = "XDB2" ]
then

    systables="sysibm.systables";
    ...
else if [ "X$db" = "XOracle" ]
then

    systables=...
    ...
else if [ "X$db" = ...

    systables=...
    ...
fi;

# extract info using variables
...

Hope it helps
/Lennart Received on Mon Jan 28 2002 - 22:05:42 CET

Original text of this message