Oracle(오라클) 테이블/컬럼 스키마 정보 조회

테이블 정보 조회 쿼리 

SELECT A.OWNER, 
       A.TABLESPACE_NAME, 
       A.TABLE_NAME, 
       A.NUM_ROWS,
       B.COMMENTS
  FROM all_all_tables A,
       USER_TAB_COMMENTS B
 WHERE 1=1
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.NUM_ROWS > 1
;

 

컬럼 정보 조회 쿼리

SELECT A.TABLE_NAME, 
       A.COLUMN_NAME, 
       B.COMMENTS,
       A.DATA_TYPE
  FROM USER_TAB_COLUMNS A,
       USER_COL_COMMENTS B
 WHERE 1=1
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
--   AND A.COLUMN_NAME LIKE '%LABEL%'
   AND A.TABLE_NAME = 'OPR_LOTBASELOG'
 ORDER BY TABLE_NAME, COLUMN_ID
;

 

테이블/컬럼 조인

SELECT A.TABLE_NAME, 
       A.COLUMN_NAME, 
       A.DATA_TYPE, 
       B.COMMENTS,
       C.OWNER,
       C.TABLESPACE_NAME,
       C.NUM_ROWS
  FROM USER_TAB_COLUMNS A,
       USER_COL_COMMENTS B,
       all_all_tables C
 WHERE 1=1
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME
   AND A.TABLE_NAME = C.TABLE_NAME
   AND A.COLUMN_NAME LIKE '%LABEL%'
;