개발을 하다보면 DB 스키마를 문서로 작성해야 할 때가 있다.
특히 공공기관에서 발주한 사업을 하다보면 사업 담당 공무원이나 감리원이 집요하게 테이블 정의서를 요구하는데, 개발만 하기도 어렵고 나 혼자 개발하는 것도 아닌데, 그 모든 변동사항을 집계해서 문서로 만들기란 쉽지 않은 일이다.
그래서 별로 고급스킬은 아니지만, 한번 작성해두면 나중에 또 쓸 일이 있을 것 같아서 USER 내의 모든 테이블을 대상으로 테이블 정의서를 생성하는 스크립트를 작성했다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Oracle / Tibero
SELECT
(SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = A.TABLE_NAME) AS TABLENAME_한글,
TABLE_NAME AS TABLENAME,
(SELECT COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME = A.TABLE_NAME AND COLUMN_NAME = A.COLUMN_NAME) AS COLUMNNAME_한글,
COLUMN_NAME AS COLUMN_NAME,
DATA_TYPE AS DATATYPE,
CASE
WHEN data_type = 'DATE' THEN 7
WHEN data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR') THEN data_length
WHEN data_type = 'NUMBER' THEN
CASE
WHEN data_precision IS NULL THEN 22
ELSE CEIL((data_precision + 2) / 2) + 1
END
ELSE data_length
END AS COLUMN_SIZE,
DECODE(NULLABLE, 'Y', '', NULLABLE) AS IS_NULL,
DECODE((SELECT COUNT(1)
FROM USER_CONS_COLUMNS CC, USER_CONSTRAINTS C
WHERE CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND C.CONSTRAINT_TYPE = 'P'
AND CC.TABLE_NAME = A.TABLE_NAME
AND CC.COLUMN_NAME = A.COLUMN_NAME), 1, 'Y', NULL) AS IS_PK,
DECODE((SELECT COUNT(1)
FROM USER_CONS_COLUMNS CC, USER_CONSTRAINTS C
WHERE CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND C.CONSTRAINT_TYPE = 'R' -- FK
AND CC.TABLE_NAME = A.TABLE_NAME
AND CC.COLUMN_NAME = A.COLUMN_NAME), 1, 'Y', NULL) AS IS_FK,
DECODE((SELECT COUNT(1)
FROM USER_IND_COLUMNS C
WHERE A.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME), 1, 'Y', NULL) AS IS_IDX
FROM USER_TAB_COLUMNS A
ORDER BY TABLENAME, COLUMN_ID;
평소에 주석을 잘 단 사람일 수록 문서 작업 시간을 줄여줄 것이다.