DB2查看用户表与指定用户表表结构

1.在dos中查看用户表

1.1查看表

DB2 LIST TABLES FOR USER

1.2 查看表结构

DB2 describe table A

2.在DB2连接工具中（这里以SQLdbx为例子）

2.1查看用户表清单
SELECT RTRIM(TABSCHEMA) AS CONTNAME, --
RTRIM(TABNAME) AS NAME,
RTRIM(TBSPACE) AS TBSPACE,
DEFINER AS DEFINER
FROM SYSCAT.TABLES
WHERE (LEFT (TABSCHEMA,3) <> 'SYS')
AND (TYPE = 'T')
ORDER BY CONTNAME, NAME FOR FETCH ONLY;

2.2查看指定表表结构
SELECT RTRIM(C.TABSCHEMA) AS CONTCONTNAME,
C.TABNAME AS CONTNAME,
C.COLNAME AS NAME,
C.REMARKS AS COMMENT,
TC.TYPE AS ISPRIM,
SECLABELNAME AS SECLABELNAME,
RTRIM(C.TYPESCHEMA) AS SOURCE_SCHEMA,
C.TYPENAME AS SOURCE_NAME,
C.LENGTH AS LENGTH,
C.SCALE AS SCALE,
C.CODEPAGE AS CODEPAGE,
C.NULLS AS NULLS,
C.COMPRESS AS COMPRESS,
C.DEFAULT AS DEFAULT,
C.GENERATED AS GENERATED,
C.TEXT AS TEXT,
C.IDENTITY AS IDENTITY,
C.LOGGED AS LOGGED,
C.COMPACT AS COMPACT,
C.PARTKEYSEQ AS PARTKEYSEQ,
RTRIM(C.DL_FEATURES) AS DL_FEATURES,
C.INLINE_LENGTH AS INLINELENGTH,
CID.START AS START,
CID.INCREMENT AS INCREMENT,
CID.CACHE AS CACHE,
CID.MINVALUE AS MINVALUE,
CID.MAXVALUE AS MAXVALUE,
CID.CYCLE AS CYCLE,
ORDER AS ORDER
FROM SYSCAT.COLUMNS C
LEFT OUTER JOIN SYSCAT.KEYCOLUSE KCU ON KCU.TABSCHEMA = C.TABSCHEMA
AND KCU.TABNAME = C.TABNAME
AND KCU.COLNAME = C.COLNAME

LEFT OUTER JOIN SYSCAT.TABCONST TC ON TC.CONSTNAME = KCU.CONSTNAME

LEFT OUTER JOIN SYSCAT.COLIDENTATTRIBUTES CID ON CID.COLNAME = C.COLNAME
AND CID.TABNAME = C.TABNAME AND CID.TABSCHEMA = C.TABSCHEMA

WHERE (LEFT (C.TABSCHEMA,3) <> 'SYS')
AND (C.TABNAME = 'TABLE_NAME') ---此处修改表名
AND (RTRIM (C.TABSCHEMA) = 'SCHEME') ---此处修改scheme
ORDER BY COLNO FOR FETCH ONLY;

 

转载于:https://www.cnblogs.com/OliverQin/p/5301860.html