CREATE OR REPLACE VIEW VW_ALL_TAB_COLS ASSELECT T1.OWNER,T1.TABLE_NAME,T1.COMMENTS,T1.COLUMN_NAME,(CASE WHEN DATA_TYPE='NUMBER' THEN (CASE WHEN DATA_SCALE=0 THEN DATA_TYPE||'('||T2.DATA_LENGTH||')' ELSE DATA_TYPE||'('||DATA_PRECISION||','||DATA_SCALE||')' END) ELSE DATA_TYPE||'('||DATA_LENGTH||')' END ) AS DATA_TYPEFROM ALL_COL_COMMENTS T1,ALL_TAB_COLS T2WHERE T1.OWNER=T2.OWNERAND T1.TABLE_NAME=T2.TABLE_NAMEAND T1.COLUMN_NAME=T2.COLUMN_NAMEAND TRIM(T1.OWNER) IN ('username')ORDER BY T2.TABLE_NAME,T2.COLUMN_ID;
desc 只可以获取到单个表的结构和描述信息,上面一个视图实现了用户下所有表的结构和comment
SELECT T1.OWNER,T1.TABLE_NAME,T1.COMMENTS,T1.COLUMN_NAME,(CASE WHEN DATA_TYPE='NUMBER' THEN (CASE WHEN DATA_SCALE=0 THEN DATA_TYPE||'('||T2.DATA_LENGTH||')' ELSE DATA_TYPE||'('||DATA_PRECISION||','||DATA_SCALE||')' END) ELSE DATA_TYPE||'('||DATA_LENGTH||')' END ) AS DATA_TYPE FROM ALL_COL_COMMENTS T1,ALL_TAB_COLS T2 WHERE T1.OWNER=T2.OWNER AND T1.TABLE_NAME=T2.TABLE_NAME AND T1.COLUMN_NAME=T2.COLUMN_NAME and t1.table_name='***' ORDER BY T2.TABLE_NAME,T2.COLUMN_ID,t2.owner;
db2 的如下:
select column_name, type_name, remarks from "SYSIBM"."SQLCOLUMNS" where table_name=? order by table_name;