/* DB 현황 파악 최종 SQL */
SELECT DCC.OWNER
, SUBSTRB(DCC.TABLE_NAME, 2, 2) SUBJECT_AREA
, DCC.TABLE_NAME
, NVL(DTC.COMMENTS, DTC.TABLE_NAME) TABLE_COMMENTS
, LENGTHB(DCC.TABLE_NAME) TABLE_LENGTH
, DCC.COLUMN_NAME
, NVL(REPLACE(REPLACE(DCC.COMMENTS, CHR(13), NULL), CHR(10), NULL), DCC.COLUMN_NAME) COLUMN_COMMENTS
, LENGTHB(DCC.COLUMN_NAME) COLUMN_LENGTH
, DTCO.DATA_TYPE
, CASE WHEN DTCO.DATA_TYPE = 'NUMBER'
THEN CASE WHEN DTCO.DATA_PRECISION||','||DTCO.DATA_SCALE IN (',', ',0')
THEN NULL
WHEN DTCO.DATA_PRECISION IS NOT NULL AND DTCO.DATA_SCALE = '0'
THEN TO_CHAR(DTCO.DATA_PRECISION)
ELSE DTCO.DATA_PRECISION||','||DTCO.DATA_SCALE
END
WHEN DTCO.DATA_TYPE = 'DATE'
THEN NULL
WHEN DTCO.DATA_TYPE LIKE 'TIMESTAMP%'
THEN NULL
WHEN DTCO.DATA_TYPE IN ('CLOB', 'BLOB', 'RAW')
THEN NULL
ELSE TO_CHAR(DTCO.DATA_LENGTH)
END DATA_LENGTH
, DTCO.DEFAULT_LENGTH
, CASE WHEN DTCO.DEFAULT_LENGTH IS NULL
THEN 'N/A'
ELSE EXTRACTVALUE( DBMS_XMLGEN.GETXMLTYPE( 'SELECT DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = ''' || DTCO.TABLE_NAME || ''' AND COLUMN_NAME = ''' || DTCO.COLUMN_NAME || '''' ), '//text()' ) --text() : 소문자여야 함. 아니면 에러 발생
END AS DATA_DEFAULT
, DTCO.COLUMN_ID
, DTCO.NULLABLE
, NVL(REPLACE(REPLACE(DCC.COMMENTS, CHR(13), NULL), CHR(10), NULL), DCC.COLUMN_NAME) COLUMN_EXPLAIN
, DC.INDEX_NAME
, DCCL.POSITION PK
, DCCL.CONSTRAINT_NAME PK_NAME
, DC.CONSTRAINT_TYPE PK_CONSTRAINT_TYPE
, (SELECT NVL(REPLACE(REPLACE(DTC.COMMENTS, CHR(13), NULL), CHR(10), NULL), DTC.TABLE_NAME) FROM SYS.DBA_TAB_COMMENTS DTC WHERE DTC.OWNER = DCCL.OWNER AND DTC.TABLE_NAME = DCCL.TABLE_NAME) RELATED_ENTITY
, (SELECT NVL(REPLACE(REPLACE(DCC.COMMENTS, CHR(13), NULL), CHR(10), NULL), DCC.COLUMN_NAME) FROM SYS.DBA_COL_COMMENTS DCC WHERE DCC.OWNER = DCCL.OWNER AND DCC.TABLE_NAME = DCCL.TABLE_NAME AND DCC.COLUMN_NAME = DCCL.COLUMN_NAME) RELATED_ATTRIBUTE
, DCF.CONSTRAINT_NAME FK_NAME
, DCF.CONSTRAINT_TYPE FK_CONSTRAINT_TYPE
FROM SYS.DBA_COL_COMMENTS DCC
LEFT OUTER JOIN SYS.DBA_TAB_COMMENTS DTC
ON DCC.OWNER = DTC.OWNER
AND DCC.TABLE_NAME = DTC.TABLE_NAME
LEFT OUTER JOIN SYS.DBA_TAB_COLUMNS DTCO
ON DCC.OWNER = DTCO.OWNER
AND DCC.TABLE_NAME = DTCO.TABLE_NAME
AND DCC.COLUMN_NAME = DTCO.COLUMN_NAME
AND DTC.OWNER = DTCO.OWNER
AND DTC.TABLE_NAME = DTCO.TABLE_NAME
LEFT OUTER JOIN SYS.DBA_CONS_COLUMNS DCCL
ON DCC.OWNER = DCCL.OWNER
AND DCC.TABLE_NAME = DCCL.TABLE_NAME
AND DCC.COLUMN_NAME = DCCL.COLUMN_NAME
AND DTCO.COLUMN_ID = DCCL.POSITION
LEFT OUTER JOIN SYS.DBA_CONSTRAINTS DC
ON DCC.OWNER = DC.OWNER
AND DCC.TABLE_NAME = DC.TABLE_NAME
AND DCC.OWNER = DC.INDEX_OWNER
AND DCCL.CONSTRAINT_NAME = DC.CONSTRAINT_NAME
LEFT OUTER JOIN SYS.DBA_CONSTRAINTS DCF
ON DCC.OWNER = DCF.OWNER
AND DCC.TABLE_NAME = DCF.TABLE_NAME
AND DCCL.CONSTRAINT_NAME = DCF.R_CONSTRAINT_NAME
WHERE DCC.OWNER NOT LIKE '%'||'SYS'||'%'
AND DCC.OWNER NOT IN ('APEX_180200', 'DBSFWUSER', 'DBSNMP', 'XDB', 'ORDDATA', 'OUTLN', 'GSMADMIN_INTERNAL')
AND DCC.TABLE_NAME NOT LIKE '%'||'BIN$'||'%'
AND DTC.TABLE_TYPE = 'TABLE'
-- AND DCC.COMMENTS LIKE '%'||'복합'||'%'
-- AND DCC.COLUMN_NAME LIKE '%'||'BLN'||'%'
-- WHERE DCC.OWNER = 'GIFTIEL'
-- WHERE DCC.OWNER = '' --특정 스키마만
-- WHERE DCC.OWNER IN ('', '') --필요한 SCHEMA OWNER만 지정
-- AND (DCC.COMMENTS LIKE '%'||'코드' OR DCC.COMMENTS LIKE '%'||'구분'||'%') --특정 논리 코멘트만
-- WHERE DCC.OWNER NOT LIKE ('%'||'SYS'||'%') AND DCC.OWNER NOT IN ('', '') --ORACLE DEFAULT 계정 제외 시
-- AND DCC.TABLE_NAME NOT LIKE '%'||'BIN$'||'%' -- 삭제 후 휴지통에 있는 테이블 제외 : DBA 권한으로 PURGE할 것
-- AND (DCC.TABLE_NAME LIKE 'TAL'||'%' OR DCC.TABLE_NAME LIKE 'TCC'||'%') --특정 주제영역 코드만 제외
-- AND DCC.COLUMN_NAME LIKE '%'||'PHC_CD'||'%'ORDER BY 1, 2, 3, 13, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23
;
