Informix iSQL has a command "
info columns for table;
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.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.)