Re: how to query the structure of a database
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