[oracle] 필드명으로 테이블 조회(user table)

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

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

 

 

 

-- 오라클 딕셔너리 정보를 담고 있는 테이블

select *

  from dict

 

 

 

//////////////  PK 조회 추가 ////////////

 

/- FORMATTED ON 2011/12/05 15:01 (FORMATTER PLUS V4.8.8) *-
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
         , DECODE(PK_CHECK.CONSTRAINT_TYPE, 'P', 'Y', '') AS PK_YN
    FROM USER_TAB_COLUMNS, USER_TAB_COMMENTS, USER_COL_COMMENTS
       , (SELECT A.TABLE_NAME
               , B.COLUMN_NAME
               , A.CONSTRAINT_NAME
               , A.CONSTRAINT_TYPE
               , A.SEARCH_CONDITION
            FROM USER_CONSTRAINTS  A
               , USER_CONS_COLUMNS B
           WHERE 1=1
             AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
             AND A.CONSTRAINT_TYPE = 'P'
             AND A.TABLE_NAME      = :TABLE_NAME) PK_CHECK
   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_COL_COMMENTS.TABLE_NAME     = PK_CHECK.TABLE_NAME(+)
     AND USER_COL_COMMENTS.COLUMN_NAME    = PK_CHECK.COLUMN_NAME(+)
     AND USER_TAB_COMMENTS.TABLE_TYPE     = 'TABLE'
     AND USER_TAB_COLUMNS.TABLE_NAME      = :TABLE_NAME
ORDER BY USER_TAB_COLUMNS.TABLE_NAME, USER_TAB_COLUMNS.COLUMN_ID