Informix SQL - List all fields & tables

Question

Informix iSQL has a command "info tables;" that shows all tables.
The syntax for viewing the fields and their respective data types is "info columns for table;"

Is there a similar command that shows table.field for all tables and all fields?

Answer 1

Using the preferred JOIN notation:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables  AS t
  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid
 WHERE t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

or the old-fashioned join-in-where-clause notation:

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column
  FROM "informix".systables AS t, "informix".syscolumns AS c
 WHERE t.tabid = c.tabid
   AND t.tabtype = 'T'
   AND t.tabid >= 100
 ORDER BY t.tabname, c.colno;

Assuming you are using a sufficiently recent version of IDS, you can order by columns not cited in the select-list. If you get complaints, add the ordering columns to the select list.

The join criterion is obvious; the tabtype = 'T' lists only tables, not views, synonyms and other such items listed in systables; the tabid >= 100 only lists tables created explicitly in the database, not the system catalog.

This does not include the type information - if you want that, you have to do a bit more work. You will find a file $INFORMIXDIR/etc/xpg4_is.sql that contains a crude approximation to an old version of the XPG4 (X/Open standard) Information Schema (hence the file name). In there, there are functions etc to decode type information from syscolumns.coltype and syscolumns.collength into recognizable strings. However, I strongly suspect it does not handle DISTINCT types, nor other user-defined types. I'll be delighted to be proved wrong, but... If you add the relevant parts of that file to your database, you should then be able to get the type information too.

Also note that all the INFO commands in ISQL and DB-Access are simulated in the front-end, not executed in the IDS server. Basically, the programs take the request and convert it into a more complex SQL statement. See the code in the file sqlinfo.ec that is part of SQLCMD (available from the IIUG Software Archive) for how my SQLCMD program handles INFO statements. (Note: the INFO output of SQLCMD is formatted differently from the INFO output of ISQL and DB-Access.)