我们如何通过SQL了解表(DB2)中每个列的描述?
我的数据库是DB2.
select tabname, colname, typename, length, scale, default, nulls, identity, generated, remarks, keyseq from syscat.columns
SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, COLUMN_TEXT FROM "SYSIBM"."SQLCOLUMNS" WHERE TABLE_SCHEM = 'SCHEMA' AND TABLE_NAME = 'TABLE'
这是在DB2 V5R4上,并不是系统表而是SYSTEM VIEW
.如果您坚持在表格列表中寻找它.
- 注意:where子句区分大小写,需要为大写
select t.table_schema as Library ,t.table_name ,t.table_type ,c.column_name ,c.ordinal_position ,c.data_type ,c.character_maximum_length as Length ,c.numeric_precision as Precision ,c.numeric_scale as Scale ,c.column_default ,t.is_insertable_into from sysibm.tables t join sysibm.columns c on t.table_schema = c.table_schema and t.table_name = c.table_name where t.table_schema = 'MYLIB' and t.table_name = 'MYTABLE' order by t.table_name, c.ordinal_position
- 获取所有元表的列表:
select * from sysibm.tables where table_schema = 'SYSIBM'