[오라클] 테이블 정의서 (table layout) 출력하기 > db

간단하게 테이블 정의서(table layout)를 쿼리로 출력한다.

TABLE_COMMENTS   (테이블 코멘트)
TABLE_NAME       (테이블명)
COLUMN_COMMENTS  (컬럼 코멘트)
COLUMN_NAME      (컬럼명)
PK_FLAG          (pk 여부)
FK_FLAG          (fk 여부)
NULL_FLAG        (null 여부)
DATA_TYPE        (테이터타입)
DATA_LENGTH      (데이터 길이)

SELECT A1.TABLE_COMMENTS TABLE_COMMENTS
     , A1.TABLE_NAME TABLE_NAME
     , A1.COLUMN_COMMENTS COLUMN_COMMENTS
     , A1.COLUMN_NAME COLUMN_NAME
     , (CASE
           WHEN B1.CONSTRAINT_TYPE = 'P'
              THEN 'Y'
        END) PK_FLAG
     , (CASE
           WHEN B1.CONSTRAINT_TYPE = 'R'
              THEN 'Y'
        END) FK_FLAG
     , A1.NULL_FLAG
     , A1.DATA_TYPE
     , A1.DATA_LENGTH
  FROM (SELECT B.COMMENTS TABLE_COMMENTS
             , A.TABLE_NAME TABLE_NAME
             , C.COMMENTS COLUMN_COMMENTS
             , A.COLUMN_NAME COLUMN_NAME
             , (CASE A.NULLABLE
                   WHEN 'Y'
                      THEN 'Y'
                END) NULL_FLAG
             , A.DATA_TYPE DATA_TYPE
             , (CASE
                   WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
                      THEN '(' || A.DATA_LENGTH || ')'
                   WHEN A.DATA_TYPE = 'NUMBER'
                   AND A.DATA_SCALE = 0
                   AND A.DATA_PRECISION IS NOT NULL
                      THEN '(' || A.DATA_PRECISION || ')'
                   WHEN A.DATA_TYPE = 'NUMBER'
                   AND A.DATA_SCALE <> 0
                      THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE
                           || ')'
                END
               ) DATA_LENGTH

             , A.COLUMN_ID
          FROM USER_TAB_COLUMNS A
             , USER_TAB_COMMENTS B
             , USER_COL_COMMENTS C
         WHERE (A.TABLE_NAME = B.TABLE_NAME)
           AND (    A.TABLE_NAME = C.TABLE_NAME
                AND A.COLUMN_NAME = C.COLUMN_NAME
               )) A1
     , (SELECT A.TABLE_NAME
             , A.COLUMN_NAME
             , B.CONSTRAINT_TYPE
          FROM USER_CONS_COLUMNS A
             , USER_CONSTRAINTS B
         WHERE (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
           AND B.CONSTRAINT_TYPE IN ('P', 'R')) B1
WHERE (    A1.TABLE_NAME = B1.TABLE_NAME(+)
        AND A1.COLUMN_NAME = B1.COLUMN_NAME(+))
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID


'

-- ansi sql을 사용해서 아래와 같이 바꾸어 보았다.

SELECT   A1.TABLE_NAME TABLE_NAME
       , A1.TABLE_COMMENTS TABLE_COMMENTS
       , A1.COLUMN_NAME COLUMN_NAME
       , A1.COLUMN_COMMENTS COLUMN_COMMENTS
       , DECODE (B1.CONSTRAINT_TYPE, 'P', 'Y') PK_FLAG
       , DECODE (B1.CONSTRAINT_TYPE, 'R', 'Y') FK_FLAG
       , A1.NULL_FLAG
       , A1.DATA_TYPE
       , A1.DATA_LENGTH
FROM     (SELECT B.COMMENTS TABLE_COMMENTS
               , A.TABLE_NAME TABLE_NAME
               , C.COMMENTS COLUMN_COMMENTS
               , A.COLUMN_NAME COLUMN_NAME
               , DECODE (A.NULLABLE, 'Y', 'Y') NULL_FLAG
               , A.DATA_TYPE DATA_TYPE
               , (CASE
                     WHEN A.DATA_TYPE IN ('CHAR', 'VARCHAR2')
                        THEN '(' || A.DATA_LENGTH || ')'
                     WHEN A.DATA_TYPE = 'NUMBER'
                  AND    A.DATA_SCALE = 0
                  AND    A.DATA_PRECISION IS NOT NULL
                        THEN '(' || A.DATA_PRECISION || ')'
                     WHEN A.DATA_TYPE = 'NUMBER'
                  AND    A.DATA_SCALE <> 0
                        THEN '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
                  END
                 ) DATA_LENGTH
               , A.COLUMN_ID
          FROM   USER_TAB_COLUMNS A JOIN USER_TAB_COMMENTS B ON (A.TABLE_NAME = B.TABLE_NAME)
                 JOIN USER_COL_COMMENTS C ON (    A.TABLE_NAME = C.TABLE_NAME
                                              AND A.COLUMN_NAME = C.COLUMN_NAME)
                 ) A1
         LEFT JOIN
         (SELECT A.TABLE_NAME, A.COLUMN_NAME, B.CONSTRAINT_TYPE
          FROM   USER_CONS_COLUMNS A, USER_CONSTRAINTS B
          WHERE  (A.CONSTRAINT_NAME = B.CONSTRAINT_NAME)
          AND    B.CONSTRAINT_TYPE IN ('P', 'R')) B1
         ON (    A1.TABLE_NAME = B1.TABLE_NAME
             AND A1.COLUMN_NAME = B1.COLUMN_NAME)
ORDER BY A1.TABLE_NAME, A1.COLUMN_ID