获取表结构和字段描述的视图

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;