[SQL] 전체 테이블 스키마

SELECT
    DECODE(COLUMN_ID, 1, OWNER, '') AS OWNER
    , DECODE(COLUMN_ID, 1, TABLE_NAME, '') AS TABLE_NAME
    , DECODE(COLUMN_ID, 1, (SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = A.TABLE_NAME AND ROWNUM=1), '') AS TABLE_NM
    , COLUMN_ID
    , COLUMN_NAME
    , (SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE COLUMN_NAME = A.COLUMN_NAME AND TABLE_NAME = A.TABLE_NAME AND ROWNUM=1) AS COLUMN_NM
    , DATA_TYPE || '(' || DATA_LENGTH || ')' as DATA_TYPE
    , NULLABLE
    , ( SELECT B.CONSTRAINT_NAME
        FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS B
        WHERE C.OWNER = B.OWNER
        AND C.TABLE_NAME = B.TABLE_NAME
        AND C.CONSTRAINT_NAME = B.CONSTRAINT_NAME
        AND C.CONSTRAINT_TYPE = 'P'
        AND B.OWNER = A.OWNER
        AND B.TABLE_NAME = A.TABLE_NAME
        AND B.COLUMN_NAME = A.COLUMN_NAME) AS PK
FROM 
(
    SELECT 
        OWNER
        , TABLE_NAME
        , COLUMN_ID
        , COLUMN_NAME
        , DATA_TYPE
        , DATA_LENGTH
        , NULLABLE 
    FROM 
        ALL_TAB_COLUMNS 
    WHERE 
        OWNER LIKE '%'
    ORDER BY 
        owner, table_name, column_id
 ) A