[Oracle] 테이블 정의서 / 명세서 쿼리

WITH LIST AS (
    SELECT A.TABLE_NAME
         , A.COLUMN_NAME
         , A.DATA_TYPE
         , A.DATA_LENGTH
         , A.NULLABLE
         , B.COMMENTS
      FROM dba_tab_columns A
         , all_col_comments B
     WHERE A.OWNER = B.OWNER
       AND A.TABLE_NAME = B.TABLE_NAME
       AND A.COLUMN_NAME = B.COLUMN_NAME
       AND A.OWNER = 'Database'   -- DB명
       AND A.TABLE_NAME = 'Table' -- TABLE명
)
 , PKLIST AS (
    SELECT C.TABLE_NAME
         , C.COLUMN_NAME
         , C.POSITION
      FROM USER_CONS_COLUMNS C
         , USER_CONSTRAINTS S
     WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME
       AND S.CONSTRAINT_TYPE = 'P'
)
SELECT L.TABLE_NAME AS "테이블명"
     , L.COLUMN_NAME AS "컬럼명"
     , L.DATA_TYPE AS "데이터타입"
     , L.DATA_LENGTH AS "길이"
     , CASE WHEN P.POSITION < 99 THEN 'Y'
            ELSE ' '
       END AS "PK"
     , L.NULLABLE AS "Null 여부"
     , L.COMMENTS AS "Comments"
  FROM LIST L
     , PKLIST P
 WHERE L.TABLE_NAME = P.TABLE_NAME(+)
   AND L.COLUMN_NAME = P.COLUMN_NAME(+)
 ORDER BY L.TABLE_NAME
     , NVL(P.POSITION, 99)
;