ORACLE 테이블 정의서 뽑는 쿼리

1/1일 이지만 프로젝트룸에 와서 작업을 하고 있네요 .
지금 진행하는 프로젝트의 DBMS 가 Oracle 이라 찾아낸 쿼리를 공유해 드리고자 합니다. 

쿼리 

-- 테이블 목록 및 상세 정보 조회
SELECT 
    a.TABLE_NAME as "테이블명",
    b.COMMENTS as "테이블설명",
    a.COLUMN_NAME as "컬럼명",
    a.DATA_TYPE || 
    CASE 
        WHEN a.DATA_TYPE IN ('VARCHAR2', 'CHAR') THEN '(' || a.DATA_LENGTH || ')'
        WHEN a.DATA_TYPE = 'NUMBER' AND a.DATA_PRECISION IS NOT NULL THEN
            '(' || a.DATA_PRECISION || 
            CASE WHEN a.DATA_SCALE > 0 THEN ',' || a.DATA_SCALE ELSE '' END || ')'
        ELSE ''
    END as "데이터타입",
    CASE a.NULLABLE WHEN 'Y' THEN 'Y' ELSE 'N' END as "NULL허용",
    c.COMMENTS as "컬럼설명",
    CASE 
        WHEN p.CONSTRAINT_TYPE = 'P' THEN 'PK'
        ELSE ''
    END as "PRIMARY KEY",
    CASE 
        WHEN f.CONSTRAINT_TYPE = 'R' THEN 'FK'
        ELSE ''
    END as "FOREIGN KEY",
    NVL(d.REFERENCED_TABLE, '') as "참조테이블",
    NVL(d.REFERENCED_COLUMN, '') as "참조컬럼",
    a.DEFAULT_LENGTH as "기본값"
FROM ALL_TAB_COLUMNS a
LEFT JOIN ALL_TAB_COMMENTS b ON a.OWNER = b.OWNER 
    AND a.TABLE_NAME = b.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS c ON a.OWNER = c.OWNER 
    AND a.TABLE_NAME = c.TABLE_NAME 
    AND a.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN (
    SELECT 
        acc.OWNER,
        acc.TABLE_NAME,
        acc.COLUMN_NAME,
        ac.CONSTRAINT_TYPE
    FROM ALL_CONS_COLUMNS acc
    JOIN ALL_CONSTRAINTS ac ON acc.OWNER = ac.OWNER 
        AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
    WHERE ac.CONSTRAINT_TYPE = 'P'
) p ON a.OWNER = p.OWNER 
    AND a.TABLE_NAME = p.TABLE_NAME 
    AND a.COLUMN_NAME = p.COLUMN_NAME
LEFT JOIN (
    SELECT 
        acc.OWNER,
        acc.TABLE_NAME,
        acc.COLUMN_NAME,
        ac.CONSTRAINT_TYPE
    FROM ALL_CONS_COLUMNS acc
    JOIN ALL_CONSTRAINTS ac ON acc.OWNER = ac.OWNER 
        AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
    WHERE ac.CONSTRAINT_TYPE = 'R'
) f ON a.OWNER = f.OWNER 
    AND a.TABLE_NAME = f.TABLE_NAME 
    AND a.COLUMN_NAME = f.COLUMN_NAME
LEFT JOIN (
    SELECT 
        a.OWNER,
        a.TABLE_NAME,
        a.COLUMN_NAME,
        c.TABLE_NAME as REFERENCED_TABLE,
        b.COLUMN_NAME as REFERENCED_COLUMN
    FROM ALL_CONS_COLUMNS a
    JOIN ALL_CONSTRAINTS c ON a.OWNER = c.OWNER 
        AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
    JOIN ALL_CONS_COLUMNS b ON c.R_OWNER = b.OWNER 
        AND c.R_CONSTRAINT_NAME = b.CONSTRAINT_NAME
    WHERE c.CONSTRAINT_TYPE = 'R'
) d ON a.OWNER = d.OWNER 
    AND a.TABLE_NAME = d.TABLE_NAME 
    AND a.COLUMN_NAME = d.COLUMN_NAME
WHERE a.OWNER = 'YOUR_SCHEMA_NAME'  -- 스키마명을 입력하세요
ORDER BY a.TABLE_NAME, a.COLUMN_ID;

위의 예시 쿼리에서 스키마명만 넣어서 뽑아보시면 아주 손쉽게 테이블 정의서를 작성하실 수 있습니다.