컬럼명(COLUMN_NAME) 또는 코멘트(COMMENT) 조회 쿼리

ORACLE 에서 컬럼명(COLUMN_NAME) 또는 코멘트(COMMENT)  조회하기 위한 쿼리 입니다.

해당 컬럼의 사용 현황이나 용어 사용 현황 등을 확인하기 위해 유용하리라 생각합니다.

COMMENT 검색은 성능상 좋지 않으니 컬럼 검색과 같이 사용하거나 사용을 지양하는 것이 좋습니다.

검색 결과는 PK 에서 사용되는 컬럼이 먼저 노출되도록 처리하였습니다.

 

-- VER.3
----------------------------------------------------------------------
-- 0.특정 컬럼 참조 테이블 조회
----------------------------------------------------------------------
-- [bind 변수1]  v_owner         : Required. 성능을 위해 필수값으로 처리.
-- [bind 변수2]  v_columns_nm    : 조회하고자 하는 컬럼명(변수2, 3 중 선택적 필수입력)
-- [bind 변수3]  v_ccComments    : 조회하고자 하는 컬럼명(변수2, 3 중 선택적 필수입력)
----------------------------------------------------------------------
--PK인 테이블
WITH SEARCH_LIST AS
(
--
SELECT
        CASE  WHEN CB.COLUMN_NAME IS NOT NULL THEN '1.PK테이블'
              ELSE '2.컬럼' END AS "구분",
        AT.OWNER,
        AT.TABLE_NAME,
        TC.COMMENTS "TABLE주석",
        CA.COLUMN_NAME,
        CA.DATA_TYPE,
        CA.DATA_LENGTH,
        CC.COMMENTS "컬럼주석"
FROM ALL_TABLES AT
  INNER JOIN ALL_TAB_COLUMNS ca
    ON CA.OWNER = AT.OWNER AND CA.TABLE_NAME = AT.TABLE_NAME
  LEFT JOIN ALL_CONSTRAINTS ac
    ON AC.OWNER = CA.OWNER AND AC.TABLE_NAME = CA.TABLE_NAME AND AC.CONSTRAINT_TYPE = 'P'
  LEFT JOIN ALL_CONS_COLUMNS  CB
    ON CB.OWNER = ac.OWNER AND CB.TABLE_NAME = ac.TABLE_NAME AND CB.CONSTRAINT_NAME = AC.CONSTRAINT_NAME
    AND CB.COLUMN_NAME = CA.COLUMN_NAME
  LEFT JOIN ALL_TAB_COMMENTS TC
    ON TC.OWNER = CA.OWNER AND TC.TABLE_NAME = CA.TABLE_NAME
  LEFT JOIN ALL_COL_COMMENTS CC
    ON CC.OWNER = CA.OWNER AND CC.TABLE_NAME = CA.TABLE_NAME AND CC.COLUMN_NAME = CA.COLUMN_NAME
WHERE 1 = 1
  AND AT.OWNER = UPPER(:v_owner)
  AND ( :v_columns_nm IS NULL OR ( :v_columns_nm IS NOT NULL AND CA.COLUMN_NAME LIKE '%'||UPPER(:v_columns_nm)||'%' ) )
  AND ( :v_ccComments IS NULL OR ( :v_ccComments IS NOT NULL AND CC.COMMENTS LIKE '%'||UPPER(:v_ccComments)||'%' ) )
--
)
SELECT
        CA."구분",
        CA.OWNER,
        CA.TABLE_NAME,
        CA."TABLE주석",
        CA.COLUMN_NAME,
        CA.DATA_TYPE,
        CA.DATA_LENGTH,
        CA."컬럼주석",
        ( SELECT LISTAGG(ACC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY ACC.POSITION ASC) PK_COLS 
          FROM ALL_CONS_COLUMNS ACC
          WHERE ACC.OWNER = CA.OWNER AND ACC.TABLE_NAME = CA.TABLE_NAME AND ACC.CONSTRAINT_NAME LIKE '%PK%'
        ) PK_COLS,
        ( SELECT LISTAGG(NVL(ACC2.COMMENTS, '_'), ',') WITHIN GROUP (ORDER BY ACC.POSITION ASC) PK_COLS_COMMENT 
          FROM ALL_CONS_COLUMNS ACC
            LEFT JOIN ALL_COL_COMMENTS ACC2
             ON ACC2.OWNER = ACC.OWNER AND ACC2.TABLE_NAME = ACC.TABLE_NAME AND ACC2.COLUMN_NAME = ACC.COLUMN_NAME
          WHERE ACC.OWNER = CA.OWNER AND ACC.TABLE_NAME = CA.TABLE_NAME AND ACC.CONSTRAINT_NAME LIKE '%PK%'
        ) "PK_COLS_주석"
FROM SEARCH_LIST CA
ORDER BY CA."구분" ASC, CA.OWNER ASC, CA.TABLE_NAME
;