Oracle 导出数据库结构语句

临时用的语句,能跑就行。

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;
Oracle表结构

我来吐槽

*

*