[오라클] DB 테이블 정의서 작성을 위해 데이터를 뽑아 보자.

1. 기준일을 정해 생성된 테이블 찾기

SELECT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND CREATED >= to_date('20120313', 'yyyyMMdd')

2. 순서, 컬럼명, 타입, 길이, NULL여부, 초기값 을 가져오기

SELECT COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, DATA_DEFAULT
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY COLUMN_ID

3. 코멘트 가져오기

SELECT * FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = 'TABLE_NAME'

4. 2번의 결과에 3번 코멘트 삽입

SELECT
COLUMN_ID
, COLUMN_NAME
,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT
, DATA_TYPE
, DATA_LENGTH
, NULLABLE
, DATA_DEFAULT
FROM ALL_TAB_COLUMNS ATC
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY COLUMN_ID

5. 테이블의 PK  알아보기

 SELECT A.OWNER || '.' || A.TABLE_NAME TABLE_NAME, A.CONSTRAINT_NAME,   
                 DECODE(A.STATUS, 'ENABLED', 'Y', NULL) ENABLED,
                 A.CONSTRAINT_TYPE, B.COLUMN_NAME, B.POSITION
   FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
 WHERE A.CONSTRAINT_TYPE IN ('P', 'R')
       AND A.TABLE_NAME = B.TABLE_NAME
       AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
       AND A.TABLE_NAME='TABLE_NAME'

6. 4번의 결과에 5번의 PK 여부 추가하기

SELECT
COLUMN_ID
, COLUMN_NAME
,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT
, DATA_TYPE
, DATA_LENGTH
, NULLABLE
, (SELECT DECODE(POSITION, 1, 'PK', 2, 'FK', '') IS_PK FROM USER_CONS_COLUMNS
            WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME
              AND POSITION IS NOT NULL
) IS_PK
, DATA_DEFAULT
FROM ALL_TAB_COLUMNS ATC
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY COLUMN_ID

7. 1번의 테이블 목록을 추가 하여 최종 결과 보기

SELECT

TABLE_NAME

, COLUMN_ID
, COLUMN_NAME
,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME ) T_COMMENT
, DATA_TYPE
, DATA_LENGTH
, NULLABLE
, (SELECT DECODE(POSITION, 1, 'PK', 2, 'FK', '') IS_PK FROM USER_CONS_COLUMNS
            WHERE TABLE_NAME = ATC.TABLE_NAME AND COLUMN_NAME = ATC.COLUMN_NAME
              AND POSITION IS NOT NULL
) IS_PK
, DATA_DEFAULT
FROM ALL_TAB_COLUMNS ATC
WHERE TABLE_NAME IN
    (SELECT OBJECT_NAME
    FROM USER_OBJECTS
    WHERE OBJECT_TYPE = 'TABLE'
    AND CREATED >= to_date('20120313', 'yyyyMMdd'))
ORDER BY TABLE_NAME, COLUMN_ID