DB2获取当前用户表、字段、索引等详细信息

SELECT

d. name tbName,

COALESCE (d.remarks, '' ) tbDesc,

a. name columnName,

a.coltype columnType ,

decode(a.nulls, 'Y' , '1' , '0' ) notNull,

decode(a.identity, 'Y' , '1' , '0' ) auto,

a.longlength width,

a.scale precision ,

COALESCE (a.remarks, '' ) comment,

decode(n.unique_colcount, '1' , '1' , '0' ) unique ,

decode(n.uniquerule, 'P' , '1' , '0' ) masterKey,

COALESCE (n. name , '' ) indexName

FROM

sysibm.syscolumns a

INNER JOIN sysibm.systables d on a.tbname=d. name

LEFT JOIN sysibm.sysindexes n on n.tbname=d. name and SUBSTR(colnames,2)=a. name

where

d.type= 'T' and d.tbspace= 'USERSPACE1'