[ORACLE/오라클] 딕셔너리 뷰를 이용해 테이블 명세서 작성

딕셔너리 뷰를 이용해서 특정 스키마에 있는 테이블들의

명세를 추출할 수 있다.

※ 테이블 정보 추출은 ALL_TABLES, ALL_TAB_COLS, ALL_TAB_COMMENTS, ALL_COL_COMMENTS

제약조건  추출은 ALL_CONSTRAINTS, ALL_CONS_COLUMNS을 이용한다. (제약조건은 PRIMARY KEY만 추출)

/* SCOTT 스키마의 테이블 정보 조회 */
SELECT A.OWNER
     , A.TABLE_NAME
     , C.COMMENTS
     , B.COLUMN_NAME
     , D.COMMENTS
     , B.DATA_TYPE
     , B.DATA_LENGTH
     , B.DATA_PRECISION
     , B.DATA_SCALE
     , B.NULLABLE
  FROM ALL_TABLES A
     , ALL_TAB_COLS B
     , ALL_TAB_COMMENTS C
     , ALL_COL_COMMENTS D
 WHERE A.DROPPED = 'NO'  --삭제된 테이블 제외
   AND A.OWNER = 'SCOTT' --스키마 입력
   AND B.HIDDEN_COLUMN = 'NO' --히든컬럼 제외
   AND B.OWNER = A.OWNER
   AND B.TABLE_NAME = A.TABLE_NAME
   AND C.OWNER = B.OWNER
   AND C.TABLE_NAME = B.TABLE_NAME
   AND D.OWNER = B.OWNER
   AND D.TABLE_NAME = B.TABLE_NAME
   AND D.COLUMN_NAME = B.COLUMN_NAME;
   
/* SCOTT 스키마의 제약조건 조회 */
SELECT A.OWNER 
     , A.TABLE_NAME
     , A.CONSTRAINT_NAME
     , B.COLUMN_NAME
     , B.POSITION
  FROM ALL_CONSTRAINTS A
     , ALL_CONS_COLUMNS B
 WHERE 1=1
   AND A.OWNER = 'SCOTT' --스키마 입력
   AND A.CONSTRAINT_TYPE IN ('R','P') --R:FK, P:PK
   AND B.OWNER = A.OWNER
   AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME;

위와 같이 쿼리를 구성하고, 두개를 JOIN으로 합치면 쿼리는 완성된다.

단, 두 쿼리는 컬럼 레벨로 조회하는 쿼리이며, 제약조건 조회 쿼리와 JOIN시 OUTER JOIN을 활용하여야 한다.

SELECT A.OWNER AS "OWNER"
     , A.TABLE_NAME AS "TABLE_NAME"
     , C.COMMENTS AS "TABLE_COMMENTS"
     , B.COLUMN_NAME AS "COLUMN_NAME"
     , D.COMMENTS AS "COLUMN_COMMENTS"
     , B.DATA_TYPE AS "DATA_TYPE"
     , CASE WHEN B.DATA_TYPE = 'NUMBER' AND B.DATA_PRECISION IS NOT NULL THEN B.DATA_PRECISION||','||B.DATA_SCALE
            ELSE TO_CHAR(B.DATA_LENGTH)
        END AS "DATA_LEGNTH"
     , DECODE(B.NULLABLE,'N','NOT NULL','NULL') AS "NULLABLE"
     , E.CONSTRAINT_NAME AS "CONSTRAINT_NAME"
     , CASE WHEN E.CONSTRAINT_NAME IS NOT NULL THEN 'PK' ELSE NULL END AS "PK"
  FROM ALL_TABLES A
     , ALL_TAB_COLS B
     , ALL_TAB_COMMENTS C
     , ALL_COL_COMMENTS D
     , (SELECT A.OWNER 
             , A.TABLE_NAME
             , A.CONSTRAINT_NAME
             , B.COLUMN_NAME
             , B.POSITION
          FROM ALL_CONSTRAINTS A
             , ALL_CONS_COLUMNS B
         WHERE 1=1 
           AND A.OWNER = 'SCOTT' --스키마 입력
           AND A.CONSTRAINT_TYPE = 'P' --제약조건 타입이 PRIMARY KEY
           AND B.OWNER = A.OWNER
           AND B.CONSTRAINT_NAME = A.CONSTRAINT_NAME) E
 WHERE A.DROPPED = 'NO'  --삭제된 테이블 제외
   AND A.OWNER = 'SCOTT' --스키마 입력
   AND B.HIDDEN_COLUMN = 'NO' --히든컬럼 제외
   AND B.OWNER = A.OWNER
   AND B.TABLE_NAME = A.TABLE_NAME
   AND C.OWNER = B.OWNER
   AND C.TABLE_NAME = B.TABLE_NAME
   AND D.OWNER = B.OWNER
   AND D.TABLE_NAME = B.TABLE_NAME
   AND D.COLUMN_NAME = B.COLUMN_NAME
   AND E.OWNER(+) = B.OWNER
   AND E.TABLE_NAME(+) = B.TABLE_NAME
   AND E.COLUMN_NAME(+) = B.COLUMN_NAME
 ORDER BY A.OWNER
     , B.TABLE_NAME
     , B.COLUMN_ID ASC

조인 후, SELECT 리스트에서 적당하게 원하는 값으로 보여지게 가공하면 테이블 명세서를 간단하게 작성할 수 있다.