Oracle查询表结构(列名、数据类型、字段注释)
Jul202020
SELECT T1.TABLE_NAME AS "表名", T3.COMMENTS AS "表说明", --T1.COLUMN_ID, T1.COLUMN_NAME AS "字段名称", T1.DATA_TYPE AS "数据类型", DECODE(T1.DATA_TYPE,'NUMBER',T1.DATA_PRECISION||','||T1.DATA_SCALE,T1.DATA_LENGTH) AS "长度", T1.NULLABLE AS "是否为空", T2.COMMENTS AS "字段说明", T1.DATA_DEFAULT "默认值" --T4.CREATED AS "建表时间" FROM COLS T1 LEFT JOIN USER_COL_COMMENTS T2 ON T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME LEFT JOIN USER_TAB_COMMENTS T3 ON T1.TABLE_NAME = T3.TABLE_NAME LEFT JOIN USER_OBJECTS T4 ON T1.TABLE_NAME = T4.OBJECT_NAME WHERE T1.TABLE_NAME = UPPER('TABLE_NAME') -- 此处表名称替换一下即可 ORDER BY T1.TABLE_NAME, T1.COLUMN_ID;