Informix SQL - 列出所有字段和表

Question

Informix SQL - 列出所有字段和表

sqlfieldinformixisql

26

Informix iSQL有一个命令 "info tables;" 可以显示所有表格。
查看字段及其相应数据类型的语法为 "info columns for table;"。

是否有类似的命令可以显示所有表格和所有字段的表.字段？

- CheeseConQueso

如果您需要其他内容，请扩展或澄清您的问题。 - Jonathan Leffler

不错，这正是我想要的，而且来自我知道会交付的人！ - CheeseConQueso

2

+1 对于使用Informix尤其是对"LEFFLER!!!! HELP!!!!"的工作经验。 :D 他太棒了! :) - Kiril Kirov

4个回答

3

正如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;

- Stephen Klancher

2

我进行了基本的加法运算来检查主键列。还添加了列顺序、数据类型长度。这为ETL生成提供了基础信息。

SELECT TRIM(t.tabname)  AS TableName
,c.colno                AS ColumnOrder
,TRIM(c.colname)        AS ColumnName
,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 Datatype
,c.collength            AS DatatypeLength
,CASE WHEN NVL(
(
    SELECT MAX(i.idxname)
    FROM "informix".sysconstraints cn inner join "informix".sysindexes i 
        on cn.idxname = i.idxname
        and cn.tabid = i.tabid
    where cn.constrtype = 'P'
        AND cn.tabid = t.tabid
        AND
        (c.colno = i.part1
        OR c.colno = i.part2
        OR c.colno = i.part3
        OR c.colno = i.part4
        OR c.colno = i.part5
        OR c.colno = i.part6
        OR c.colno = i.part7
        OR c.colno = i.part8
        OR c.colno = i.part9
        OR c.colno = i.part10
        OR c.colno = i.part11
        OR c.colno = i.part12
        OR c.colno = i.part13
        OR c.colno = i.part14
        OR c.colno = i.part15
        OR c.colno = i.part16
        )
),'') = '' THEN 0 ELSE 1 END    AS PK
FROM "informix".systables  AS t  JOIN "informix".syscolumns AS c 
    ON t.tabid = c.tabid
WHERE t.tabtype = 'T'
    AND t.tabid >= 100
    --AND t.tabname = 'resource'
ORDER BY t.tabname, c.colno;

- user14302441

请注意，DECIMAL、MONEY、DATETIME和INTERVAL类型的长度编码非常复杂。即使VARCHAR和NVARCHAR也被命名为编码，但很少遇到最小长度不为零的列，因此编码通常是不可见的。 - Jonathan Leffler

1

使用syscolumns表。这些信息在IBM Informix SQL指南中有描述。

我编写了简单的Python工具，用于显示Informix、Oracle和PostgreSQL的模式信息。如果您需要比较数据库，它们非常有用。

- Michał Niklas

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jonathan Leffler · Accepted Answer

使用首选的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;

或者老式的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;

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

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

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

请注意，ISQL和DB-Access中的所有INFO命令都是在前端模拟的，而不是在IDS服务器上执行。基本上，程序会将请求转换为更复杂的SQL语句。有关我的SQLCMD程序如何处理INFO语句，请参见SQLCMD（可从IIUG软件存档获取）中的文件sqlinfo.ec中的代码。(注意：SQLCMD的INFO输出与ISQL和DB-Access的INFO输出格式不同。)