컬럼명으로 테이블 찾기 예제

 

 

집주소 관련된 예상되는 컬럼명에 대한 테이블 뽑기

SELECT B.TABLE_NAME,        B.COLUMN_NAME,        COMMENTS
FROM   ALL_COL_COMMENTS A,
       ALL_TAB_COLUMNS B
WHERE  A.TABLE_NAME = B.TABLE_NAME
  AND  A.COLUMN_NAME = B.COLUMN_NAME
  AND (UPPER(B.COLUMN_NAME) LIKE '%ZIP%'
   OR  UPPER(B.COLUMN_NAME) LIKE '%DNG%'
   OR  UPPER(B.COLUMN_NAME) LIKE '%ADDR%'
   OR  COMMENTS LIKE '%주소%')
  AND (UPPER(B.COLUMN_NAME) NOT LIKE '%IP%'
   OR  UPPER(B.COLUMN_NAME) NOT LIKE '%MAIL%'
   OR  UPPER(B.COLUMN_NAME) NOT LIKE '%WEB%'
   OR  UPPER(COMMENTS) NOT LIKE '%IP%'
   OR  UPPER(COMMENTS) NOT LIKE '%MAIL%'
   OR  UPPER(COMMENTS) NOT LIKE '%WEB%'
   OR  COMMENTS NOT LIKE '%홈페이지%')
ORDER BY TABLE_NAME ;

 

 

컬럼명으로 테이블 찾기

SELECT TABLE_NAME,        COLUMN_NAME
FROM   ALL_TAB_COLUMNS
WHERE  COLUMN_NAME LIKE '%###%'

 

 

테이블의 커맨트 뽑기

SELECT *
FROM   ALL_COL_COMMENTS
WHERE  TABLE_NAME = '테이블명';