ORACLE SCHEMA 조회

시스템 테이블

ALL_TABLES

ALL_TAB_COMMENTS

ALL_TAB_COLUMNS

ALL_COL_COMMENTS

 

사용자 테이블

USER_OBJECTS

COLS

 

예)

SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_ID, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH
  FROM ALL_TAB_COLUMNS A
 WHERE A.OWNER = 'SWWEB'
   AND A.DATA_TYPE = 'VARCHAR2'
   AND A.DATA_LENGTH >= 14

 

SELECT A.TABLE_NAME, A.COLUMN_ID, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH
  FROM COLS A
 WHERE A.DATA_TYPE = 'VARCHAR2'
   AND A.DATA_LENGTH >= 14

 

-- PK 조회
SELECT A.TABLE_NAME,
       B.COLUMN_ID,
       B.COLUMN_NAME,
       B.DATA_TYPE,
       B.DATA_LENGTH,
       B.NULLABLE,
       B.DATA_DEFAULT ,
       (SELECT A1.CONSTRAINT_TYPE
          FROM ALL_CONSTRAINTS A1,
               ALL_CONS_COLUMNS A2
         WHERE A1.owner           = A.OWNER
           AND A1.table_name      = A.TABLE_NAME
           AND A1.CONSTRAINT_TYPE = 'P'
           AND A1.OWNER           = A2.OWNER
           AND A1.TABLE_NAME      = A2.TABLE_NAME
           AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME
           AND A2.COLUMN_NAME     = B.COLUMN_NAME
           ) IS_PK
  FROM ALL_TABLES A,
       ALL_TAB_COLUMNS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.OWNER      = B.OWNER
   AND A.owner      = 'USER_ID'
   AND A.table_name = '테이블명'
 ORDER BY B.TABLE_NAME, B.COLUMN_ID
;

-- index 조회
SELECT table_name, index_name, un,
       REPLACE(SUBSTR(MAX(sys_connect_by_path(column_name,'/')),2),'/',',') index_columns
  FROM (SELECT i.table_name,
               c.index_name,
               i.uniqueness un,
               c.column_name,
               c.column_position rn
          FROM user_indexes i,
               user_ind_columns c
         WHERE i.index_name = c.index_name
           AND i.table_name = '테이블명' )
  START WITH rn               = 1
  CONNECT BY prior index_name = index_name
             AND prior rn + 1 = rn
 GROUP BY table_name, index_name, un
 ORDER BY table_name, un DESC, index_name
;