Informix SQL - 列出所有字段和表

Informix SQL - List all fields & tables

提问by CheeseConQueso

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

Informix iSQL 有一个命令“ info tables;”，用于显示所有表。
查看字段及其各自数据类型的语法为“ info columns for table;”

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

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

回答by Jonathan Leffler

Using the preferred JOIN notation:

使用首选的 JOIN 表示法：

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:

或老式的 join-in-where-clause 表示法：

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.

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

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.

加入标准很明显；tabtype = 'T' 只列出表，不列出视图、同义词和其他列在 systables 中的项目；tabid >= 100 只列出在数据库中显式创建的表，而不是系统目录。

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.sqlthat 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.coltypeand syscolumns.collengthinto 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.

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

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

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

回答by Micha? Niklas

Use syscolumns table. Such information is described in IBM Informix Guide to SQL

使用 syscolumns 表。IBM Informix Guide to SQL 中描述了此类信息

I have done simple Python utilities that shows schema info for Informix, Oracleand PostgreSQL. They are useful if you have to compare databases.

我已经完成了简单的 Python 实用程序，可以显示Informix、Oracle和PostgreSQL 的架构信息。如果您必须比较数据库，它们会很有用。

回答by Stephen Klancher

As Jonathan Leffer's answer mentions, a full handling of column types and column details gets complicated as can be seen in the the SYSCOLUMNSdocumentation. But if you are looking at database not using more complicated types, this addition to his script will show the basic type and whether NULLs are allowed:

正如 Jonathan Leffer 的回答所提到的，对列类型和列详细信息的完整处理变得复杂，如SYSCOLUMNS文档中所见。但是，如果您正在查看不使用更复杂类型的数据库，那么添加到他的脚本中将显示基本类型以及是否允许 NULL：

SELECT TRIM(t.tabname) || '.' || TRIM(c.colname) AS table_dot_column,
CASE 
  WHEN MOD(coltype,256)=0 THEN 'CHAR' 
  WHEN MOD(coltype,256)=1 THEN 'SMALLINT' 
  WHEN MOD(coltype,256)=2 THEN 'INTEGER' 
  WHEN MOD(coltype,256)=3 THEN 'FLOAT' 
  WHEN MOD(coltype,256)=4 THEN 'SMALLFLOAT' 
  WHEN MOD(coltype,256)=5 THEN 'DECIMAL' 
  WHEN MOD(coltype,256)=6 THEN 'SERIAL' 
  WHEN MOD(coltype,256)=7 THEN 'DATE' 
  WHEN MOD(coltype,256)=8 THEN 'MONEY' 
  WHEN MOD(coltype,256)=9 THEN 'NULL' 
  WHEN MOD(coltype,256)=10 THEN 'DATETIME' 
  WHEN MOD(coltype,256)=11 THEN 'BYTE' 
  WHEN MOD(coltype,256)=12 THEN 'TEXT' 
  WHEN MOD(coltype,256)=13 THEN 'VARCHAR' 
  WHEN MOD(coltype,256)=14 THEN 'INTERVAL' 
  WHEN MOD(coltype,256)=15 THEN 'NCHAR' 
  WHEN MOD(coltype,256)=16 THEN 'NVARCHAR' 
  WHEN MOD(coltype,256)=17 THEN 'INT8' 
  WHEN MOD(coltype,256)=18 THEN 'SERIAL8' 
  WHEN MOD(coltype,256)=19 THEN 'SET' 
  WHEN MOD(coltype,256)=20 THEN 'MULTISET' 
  WHEN MOD(coltype,256)=21 THEN 'LIST' 
  WHEN MOD(coltype,256)=22 THEN 'ROW (unnamed)' 
  WHEN MOD(coltype,256)=23 THEN 'COLLECTION' 
  WHEN MOD(coltype,256)=40 THEN 'LVARCHAR fixed-length opaque types' 
  WHEN MOD(coltype,256)=41 THEN 'BLOB, BOOLEAN, CLOB variable-length opaque types' 
  WHEN MOD(coltype,256)=43 THEN 'LVARCHAR (client-side only)' 
  WHEN MOD(coltype,256)=45 THEN 'BOOLEAN' 
  WHEN MOD(coltype,256)=52 THEN 'BIGINT' 
  WHEN MOD(coltype,256)=53 THEN 'BIGSERIAL' 
  WHEN MOD(coltype,256)=2061 THEN 'IDSSECURITYLABEL'
  WHEN MOD(coltype,256)=4118 THEN 'ROW (named)' 
  ELSE TO_CHAR(coltype)
END AS Type,
BITAND(coltype,256)=256 AS NotNull
  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;