DB설계 - 물리DB 설계 점검 SQL

물리DB설계 진행시 설계 후 코멘트와 PK, Data Type을 점검해야할 때가 있다. 이럴 때 유용하게 활용할 수 있는 SQL 이며, 물리DB 설계 점검 SQL을 통해 코멘트와 PK, PK개수, 컬럼타입의 동일여부를 체크할 수 있다.

1. 테이블/컬럼에 한글 COMMENT - 코멘트출력

/* 테이블/컬럼에 한글 COMMENT - 코멘트출력*/
/* TABLE */
SELECT A.TABLE_NAME, B.COMMENTS,A.OWNER
FROM DBA_TABLES A
,DBA_TAB_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.OWNER ='WMSERP'
ORDER BY 2 NULLS FIRST, A.TABLE_NAME
;

 

/* COLUMN */
SELECT A.TABLE_NAME, B.COLUMN_NAME, C.COMMENTS,A.OWNER
FROM DBA_TABLES A
,DBA_TAB_COLUMNS B
,DBA_COL_COMMENTS C
WHERE A.TABLE_NAME = B.TABLE_NAME
AND B.TABLE_NAME = C.TABLE_NAME(+)
AND B.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER ='WMSERP'
ORDER BY 3 NULLS FIRST
;

 

2. PK 컬럼 존재여부 - 없는 리스트

/* PK 컬럼 존재여부 - 없는 리스트 */
 SELECT a.table_name, b.comments,b.owner
 FROM DBA_tables a, DBA_tab_comments b
 WHERE a.table_name = b.table_name(+)
 AND b.owner IN ('WMSERP')
 AND not exists (SELECT 'a'
                  FROM DBA_CONSTRAINTS
                  WHERE OWNER IN ('WMSERP')
                  AND CONSTRAINT_TYPE ='P'
                  and table_name = a.table_name
                  )
 ORDER BY a.table_name
 ;

 

3. PK 컬럼수 - 6개초과 리스트

 /* PK 컬럼수 - 6개초과 리스트 */

 SELECT TABLE_NAME, MAX(POSITION) PK_CNT
 FROM DBA_CONS_COLUMNS
 WHERE OWNER IN ('WMSERP')
 AND CONSTRAINT_NAME LIKE 'PK%'
 GROUP BY TABLE_NAME
 HAVING MAX(POSITION) > 6
 ORDER BY PK_CNT DESC
 ;

 

4. 테이블 속성 검증(동일 컬럼명의 상이한 Data Type) 

/* 테이블 속성 검증(동일 컬럼명의 상이한 Data Type) */

 SELECT table_name, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,OWNER
 FROM DBA_TAB_COLUMNS
 WHERE OWNER IN ('WMSERP')
 AND COLUMN_NAME IN (
 		SELECT COLUMN_NAME
    FROM (
    			SELECT B.COLUMN_NAME, B.DATA_TYPE, B.DATA_LENGTH,A.OWNER
          FROM DBA_TABLES A, DBA_TAB_COLUMNS B
          WHERE A.OWNER = B.OWNER
          AND A.TABLE_NAME = B.table_name
          AND A.OWNER IN ('WMSERP')
          GROUP BY B.COLUMN_NAME, B.DATA_TYPE, B.DATA_LENGTH,A.OWNER
          )
          GROUP BY COLUMN_NAME
          HAVING COUNT(1) > 1
    )
 ORDER BY COLUMN_NAME
 ;