테이블 정의서 추출

SELECT
    c.table_name AS "테이블ID",
    obj_description((format('%I.%I', c.table_schema, c.table_name))::regclass) AS "테이블명",
    col_description((format('%I.%I', c.table_schema, c.table_name))::regclass, c.ordinal_position) AS "컬럼명",
    c.column_name AS "컬럼ID",
    CASE
        WHEN c.data_type IN ('character varying', 'varchar', 'char')
        THEN c.data_type || '(' || c.character_maximum_length::text || ')'
        WHEN c.data_type = 'numeric' AND c.numeric_scale IS NOT NULL
        THEN c.data_type || '(' || c.numeric_precision::text || ',' || c.numeric_scale::text || ')'
        ELSE c.data_type
    END AS "타입 및 길이",
    CASE
        WHEN c.is_nullable = 'YES' THEN 'NULL'
        ELSE 'NOT NULL'
    END AS "NULL",
    --COALESCE(tc.constraint_type, 'No Constraint') AS "Constraint Type",
    (CASE
        WHEN EXISTS (SELECT 1 FROM information_schema.table_constraints AS tc_inner
                     WHERE tc_inner.table_schema = c.table_schema
                     AND tc_inner.table_name = c.table_name
                     AND tc_inner.constraint_type = 'PRIMARY KEY'
                     AND c.column_name = ANY (SELECT k.column_name
                                              FROM information_schema.key_column_usage AS k
                                              WHERE k.constraint_name = tc_inner.constraint_name
                                              AND k.table_schema = tc_inner.table_schema
                                              AND k.table_name = tc_inner.table_name))
        THEN 'Yes'
        ELSE 'No'
    END) AS "PK",
    (CASE
        WHEN EXISTS (SELECT 1 FROM information_schema.table_constraints AS tc_inner
                     WHERE tc_inner.table_schema = c.table_schema
                     AND tc_inner.table_name = c.table_name
                     AND tc_inner.constraint_type = 'FOREIGN KEY'
                     AND c.column_name = ANY (SELECT k.column_name
                                              FROM information_schema.key_column_usage AS k
                                              WHERE k.constraint_name = tc_inner.constraint_name
                                              AND k.table_schema = tc_inner.table_schema
                                              AND k.table_name = tc_inner.table_name))
        THEN 'Yes'
        ELSE 'No'
    END) AS "FK"
FROM
    information_schema.columns c
LEFT JOIN
    information_schema.table_constraints tc
ON
    c.table_schema = tc.table_schema
    AND c.table_name = tc.table_name
    AND c.column_name = ANY (SELECT k.column_name
                             FROM information_schema.key_column_usage AS k
                             WHERE k.constraint_name = tc.constraint_name
                             AND k.table_schema = tc.table_schema
                             AND k.table_name = tc.table_name)
WHERE
    c.table_schema = '스키마명'  -- Specify your schema
ORDER BY
    c.table_name, c.ordinal_position;

 

 

자동 생성 엑셀 다운로드 : https://blog.naver.com/acrodev/222227877437

[Oracle] SQL과 엑셀 매크로를 이용한 테이블 정의서(설계서) 만들기