Informix iSQL有一个命令 "
查看字段及其相应数据类型的语法为 "
info tables;
" 可以显示所有表格。查看字段及其相应数据类型的语法为 "
info columns for table;
"。
是否有类似的命令可以显示所有表格和所有字段的表.字段?info tables;
" 可以显示所有表格。info columns for table;
"。
是否有类似的命令可以显示所有表格和所有字段的表.字段?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;
sqlinfo.ec
中的代码。(注意:SQLCMD的INFO输出与ISQL和DB-Access的INFO输出格式不同。)c.colname ='COLUMNNAME'
。 - Vinay正如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;
我进行了基本的加法运算来检查主键列。 还添加了列顺序、数据类型长度。 这为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;
使用syscolumns表。这些信息在IBM Informix SQL指南中有描述。
我编写了简单的Python工具,用于显示Informix、Oracle和PostgreSQL的模式信息。如果您需要比较数据库,它们非常有用。