테이블 정보 조회

1. 테이블 정보 조회

SELECT ACC.TABLE_NAME
    , ATC.COMMENTS
    , ACC.COLUMN_NAME
    , SUBSTR(REPLACE(INITCAP('z'||ACC.COLUMN_NAME), '_', ''), 2) COLUMN_NAME_CAMEL -- MY_COL -> myCol
    , ACC.COMMENTS || NVL2(ACC2.POSITION, '(' || DECODE(AC.CONSTRAINT_TYPE, 'P', 'PK', AC.CONSTRAINT_TYPE) || ACC2.POSITION || ')',NULL) COMMENTS
    , ATC2.DATA_TYPE
    , NVL2(ATC2.DATA_PRECISION, ATC2.DATA_PRECISION || ',' || ATC2.DATA_SCALE, ATC2.DATA_LENGTH) DATA_LENGTH
FROM ALL_TAB_COMMENTS ATC
INNER JOIN ALL_TAB_COLS ATC2
ON ATC.OWNER = ATC2.OWNER
AND ATC.TABLE_NAME = ATC2.TABLE_NAME
INNER JOIN ALL_COL_COMMENTS ACC
ON ATC2.OWNER = ACC.OWNER
AND ATC2.TABLE_NAME = ACC.TABLE_NAME
AND ATC2.COLUMN_NAME = ACC.COLUMN_NAME
LEFT OUTER JOIN (
    SELECT *
    FROM ALL_CONS_COLUMNS
    WHERE POSITION IS NOT NULL) ACC2
ON ACC.OWNER = ACC2.OWNER
AND ACC.TABLE_NAME = ACC2.TABLE_NAME
AND ACC.COLUMN_NAME = ACC2.COLUMN_NAME
LEFT OUTER JOIN ALL_CONSTRAINTS AC
ON AC.OWNER = ACC2.OWNER
AND AC.CONSTRAINT_NAME = ACC2.CONSTRAINT_NAME
AND AC.TABLE_NAME = ACC2.TABLE_NAME
WHERE ACC.OWNER = 'MY_OWNER'
--AND ACC.TABLE_NAME = 'MY_TABLE'
ORDER BY ATC.TABLE_NAME, ATC2.COLUMN_ID
;

2. 테이블 비교

컬럼이 어떤 게 바뀌었는지 확인할때 사용

CNT가 1이면 한 테이블에만 있는 칼럼

SELECT ATC.TABLE_NAME
    , ATC.COLUMN_NAME
    , ACC.COMMENTS
    , COUNT(1) OVER(PARTITION BY ATC.COLUMN_NAME) CNT
FROM ALL_TAB_COLUMNS ATC
INNER JOIN ALL_COL_COMMENTS ACC
ON ATC.OWNER = ACC.OWNER
AND ATC.TABLE_NAME = ACC.TABLE_NAME
AND ATC.COLUMN_NAME = ACC.COLUMN_NAME
WHERE ATC.TABLE_NAME IN ('MY_TABLE', 'MY_TABLE_BACK')
ORDER BY CNT, ATC.TABLE_NAME, ATC.COLUMN_ID
;