필드명 테이블 명으로 테이블 조회

해당 필드명이 들어간 테이블 조회

SELECT
              USER_TAB_COLUMNS.TABLE_NAME as TNAME,   
              USER_TAB_COMMENTS.COMMENTS as TCMT,
              USER_TAB_COLUMNS.COLUMN_NAME as COL_NM,   
              USER_COL_COMMENTS.COMMENTS as COL_CMT,   
              USER_TAB_COLUMNS.DATA_TYPE as TYPE_CD,   
              DECODE(USER_TAB_COLUMNS.DATA_TYPE,'NUMBER',USER_TAB_COLUMNS.DATA_PRECISION ||'.'||USER_TAB_COLUMNS.DATA_SCALE,
             USER_TAB_COLUMNS.DATA_LENGTH) as LENGTH,
             USER_TAB_COLUMNS.NULLABLE as NULL_YN
   FROM USER_TAB_COLUMNS,
             USER_TAB_COMMENTS,
             USER_COL_COMMENTS
 WHERE USER_TAB_COLUMNS.TABLE_NAME  = USER_TAB_COMMENTS.TABLE_NAME
          AND USER_TAB_COLUMNS.TABLE_NAME  = USER_COL_COMMENTS.TABLE_NAME
          AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
          AND USER_TAB_COMMENTS.TABLE_TYPE = 'TABLE' -- 테이블만 해당
          AND ( USER_TAB_COLUMNS.COLUMN_NAME  = 'BSCODE'
                OR USER_TAB_COLUMNS.COLUMN_NAME  = 'BUSEOCODE' )
        ORDER BY USER_TAB_COLUMNS.TABLE_NAME, USER_TAB_COLUMNS.COLUMN_ID  

 

 

-- 사용자 테이블 정보
SELECT * FROM USER_TAB_COLUMNS

SELECT * FROM USER_TABLES

SELECT * FROM USER_TAB_COMMENTS

-- 사용자 컬럼 정보
SELECT * FROM USER_TAB_COLUMNS

SELECT * FROM ALL_TAB_COLUMNS

SELECT * FROM USER_COL_COMMENTS
 where 1=1
--   and comments like '%휴게소%'
   and column_name like '%HGS%'

-- 제약정보 
SELECT * FROM USER_CONSTRAINTS