Informix SQL-列出所有字段&桌子 [英] Informix SQL - List all fields & tables

问题描述

Informix iSQL具有显示所有表的命令"info tables;".
查看字段及其各自的数据类型的语法为"info columns for table;"

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;"

是否有类似的命令显示所有表和所有字段的table.field?

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

推荐答案

使用首选的JOIN表示法:

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;

或老式的子句联接:

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;

假设您使用的是最新版本的IDS，则可以按未在选择列表中引用的列进行排序.如果您有投诉，请将订购列添加到选择列表中.

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.

连接标准很明显； tabtype ='T'仅列出表，而不列出视图，同义词和systables中列出的其他此类项目； tabid> = 100仅列出在数据库中显式创建的表，而不列出系统目录.

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.

这不包括类型信息-如果需要，您需要做更多的工作.您会发现文件$INFORMIXDIR/etc/xpg4_is.sql，其中包含与XPG4(X/Open标准)信息架构的旧版本的粗略近似(因此为文件名).在那里，有功能等将syscolumns.coltype和syscolumns.collength中的类型信息解码为可识别的字符串.但是，我强烈怀疑它不处理DISTINCT类型，也不处理其他用户定义的类型.我很高兴被证明是错误的，但是...如果您将该文件的相关部分添加到数据库中，那么您也应该能够获取类型信息.

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.

还要注意，ISQL和DB-Access中的所有INFO命令都是在前端模拟的，而不是在IDS服务器中执行的.基本上，程序接受请求并将其转换为更复杂的SQL语句.请参阅文件sqlinfo.ec中的代码，该文件是SQLCMD的一部分(可从 IIUG Software Archive 获得)有关我的SQLCMD程序如何处理INFO语句的信息. (注意:SQLCMD的INFO输出格式与ISQL和DB-Access的INFO输出格式不同.)

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.)

这篇关于Informix SQL-列出所有字段&桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！