[DB] Informix System Columns 應用

select * from SYSTABLES, SYSCOLUMNS 

where tabname your condition

and SYSTABLES.tabid = SYSCOLUMNS.tabid

因為工作環境關係, 用WHERE 條件式的JOIN 語法.

Table 1. The SYSCOLUMNS table

Column	Type	Explanation
colname	VARCHAR(128)	Column name
tabid	INTEGER	Identifying code of table containing the column
colno	SMALLINT	Column number The system sequentially assigns this (from left to right within each table).
coltype	SMALLINT	Code indicating the data type of the column: 0 = CHAR 1 = SMALLINT 2 = INTEGER 3 = FLOAT 4 = SMALLFLOAT 5 = DECIMAL 6 = SERIAL 1 7 = DATE 8 = MONEY 9 = NULL 10 = DATETIME 11 = BYTE 12 = TEXT 13 = VARCHAR 14 = INTERVAL 15 = NCHAR 16 = NVARCHAR 17 = INT8 18 = SERIAL8 1 19 = SET 20 = MULTISET 21 = LIST 22 = ROW (unnamed) 23 = COLLECTION 40 = LVARCHAR fixed-length opaque types 2 41 = BLOB, BOOLEAN, CLOB variable-length opaque types 2 43 = LVARCHAR (client-side only) 45 = BOOLEAN 52 = BIGINT 53 = BIGSERIAL 1 2061 = IDSSECURITYLABEL 2, 3 4118 = ROW (named)
collength	Any of the following data types: Integer-based Varying-length character Time Fixed-point Simple-large-object IDSSECURITYLABEL	The value depends on the data type of the column. For some data types, the value is the column length (in bytes). See Storing Column Length for more information.
colmin	INTEGER	Minimum column length (in bytes)
colmax	INTEGER	Maximum column length (in bytes)
extended_id	INTEGER	Data type code, from the sysxtdtypestable, of the data type specified in the coltype column
seclabelid	INTEGER	The label ID of the security label associated with the column if it is a protected column. NULL otherwise.
colattr	SMALLINT	HIDDEN 1 - Hidden column ROWVER 2 - Row version column ROW_CHKSUM 4 - Row key column ER_CHECKVER 8 - ER row version column UPGRD1_COL 16 - ER auto primary key column UPGRD2_COL 32 - ER auto primary key column UPGRD3_COL 64 - ER auto primary key column PK_NOTNULL 128 - NOT NULL by PRIMARY