临时用的语句,能跑就行。
SELECT
T1.TABLE_NAME AS "表名",
T3.COMMENTS AS "表注释",
T1.COLUMN_NAME AS "列名",
T2.COMMENTS AS "列注释",
T1.NULLABLE AS "是否为空",
T1.DATA_DEFAULT AS "默认值",
T1.DATA_TYPE AS "字段类型",
T1.DATA_LENGTH AS "字段长度",
NVL2( T7.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否主键",
NVL2( T8.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否外键"
FROM
ALL_TAB_COLUMNS T1
LEFT JOIN ALL_COL_COMMENTS T2 ON T1.OWNER = T2.OWNER
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
LEFT JOIN ALL_TAB_COMMENTS T3 ON T1.OWNER = T3.OWNER
AND T1.TABLE_NAME = T3.TABLE_NAME
LEFT JOIN (
SELECT
T5.OWNER,
T5.TABLE_NAME,
T5.COLUMN_NAME,
T6.CONSTRAINT_TYPE
FROM
ALL_CONS_COLUMNS T5
INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER
AND T5.TABLE_NAME = T6.TABLE_NAME
AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME
AND T6.CONSTRAINT_TYPE = 'P'
) T7 ON T1.OWNER = T7.OWNER
AND T1.TABLE_NAME = T7.TABLE_NAME
AND T1.COLUMN_NAME = T7.COLUMN_NAME
LEFT JOIN (
SELECT
T5.OWNER,
T5.TABLE_NAME,
T5.COLUMN_NAME,
T6.CONSTRAINT_TYPE
FROM
ALL_CONS_COLUMNS T5
INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER
AND T5.TABLE_NAME = T6.TABLE_NAME
AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME
AND T6.CONSTRAINT_TYPE = 'R'
) T8 ON T1.OWNER = T8.OWNER
AND T1.TABLE_NAME = T8.TABLE_NAME
AND T1.COLUMN_NAME = T8.COLUMN_NAME
WHERE
T1.OWNER = 'XXXXXXXX' --要查询的用户名
AND NOT EXISTS (
SELECT
T4.OBJECT_NAME
FROM
ALL_OBJECTS T4
WHERE
T4.OWNER = T1.OWNER
AND T4.OBJECT_TYPE = 'TABLE'
AND T4.TEMPORARY = 'Y'
AND T4.OBJECT_NAME = T1.TABLE_NAME
)
ORDER BY
T1.TABLE_NAME,
T1.COLUMN_ID;