meta data structure where?
Hello, I'am comming from the Oracle world and I'am searching an equivalent for: "SYS.ALL_TABLES" => a view or table that list all tables for my database. If you can also post the differences in SQL statements between Oracle/informix that would be great! thanks! Xavier Autret
> => a view or table that list all tables for my database. Each database contains one table called systables. "select tabname from systables" gives you the name of all the tables "select tabname from systables where tabid > 99" gives you the name of all the tables other than system tables (i.e. the tables that have been created by some user). Philippe
On Wed, 14 Jan 2004 04:40:32 -0500, Xavier Autret wrote:
The direct answer, to list all of the tables in a single database use
SELECT tabnames FROM systables;
while connected to the database in question, has already been give, but that
may not be what you are looking for. Since IDS manages multiple databases in
a single instance (a trick Oracle cannot accomplish) you MAY want to list all
of the tables in all of the databases, which is what I think Vladislav was
trying to get at. That query would be:
SELECT dbsname, tabname
FROM sysmaster:systabnames
ORDER BY dbsname, tabname;
Also do not forget the dbaccess INFO command:
INFO TABLES;
INFO COLUMNS FOR tablename;
Check out the Guide to SQL Reference for the structure of the Informix system
catalog and the Administrator's Guide for details of the public tables and
views contained in the sysmaster SMI database (and the file
$INFORMIXDIR/etc/sysmaster.sql for details of the unpublished tables and
views contained in that database).
Art S. Kagel
> Hello,
>
> I'am comming from the Oracle world and I'am searching an equivalent for:
>
> "SYS.ALL_TABLES"
>
> => a view or table that list all tables for my database.
>
> If you can also post the differences in SQL statements between
> Oracle/informix that would be great!
>
> thanks!
>
> Xavier Autret