[ORACLE/TIBERO] 테이블/인덱스/제약조건 등 오브젝트 조회 쿼리

테이블정의서 등 프로젝트 끝자락에 산출물 작성할 때 유용하게 사용하는 쿼리입니다. 

 

** 테이블

SELECT a.table_name 테이블ID
     , b.comments 테이블명
     , a.column_id
     , a.column_name 컬럼ID
     , d.comments 컬럼명
     , a.data_type 데이터타입
     , a.data_length 길이
     , a.nullable NULL여부
     , a.data_default
     , decode(x.constraint_type,'P','Y','') PK
     , decode(x.constraint_type,'R','Y','') FK
     , c.tablespace_name 
FROM USER_TAB_COLUMNS a
   , USER_TAB_COMMENTS b
   , USER_COL_COMMENTS d
   , USER_TABLES c
   , (SELECT  A.TABLE_NAME, A.COLUMN_NAME, A.POSITION, B.CONSTRAINT_TYPE, A.CONSTRAINT_NAME 
      FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B　　  
      WHERE A.TABLE_NAME LIKE '%' 
      AND   A.TABLE_NAME = B.TABLE_NAME　　  
      AND   A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
      AND   B.CONSTRAINT_TYPE IN ('P','R')) x 
WHERE a.TABLE_NAME LIKE '%' 
AND a.table_name = b.table_name  
AND a.table_name = c.table_name  
AND a.table_name = d.table_name 
AND a.column_name = d.column_name 
AND a.table_name = x.table_name (+) 
AND a.column_name = x.column_name (+) 
ORDER BY a.table_name, a.column_id; 

 

** 테이블 #2

SELECT *         
FROM (SELECT C.COMMENTS 테이블한글명,        
             A.TABLE_NAME 테이블명,
             A.COLUMN_ID,       
             A.COLUMN_NAME 컬럼명,        
             B.COMMENTS 컬럼한글명,        
             DECODE(A.NULLABLE, 'Y', '', 'Y') NOTNULL여부,        
             A.DATA_TYPE 데이터타입,
             DECODE(A.DATA_TYPE, 'NUMBER', DECODE(A.DATA_PRECISION, '', '', A.DATA_PRECISION||DECODE(A.DATA_SCALE, 0, '', ','||A.DATA_SCALE)), A.DATA_LENGTH)  길이,        
             (SELECT DECODE(CONSTRAINT_TYPE, 'P', 'PK', '')         
              FROM ALL_CONSTRAINTS D,        
                   ALL_CONS_COLUMNS E        
              WHERE E.TABLE_NAME = A.TABLE_NAME        
                AND E.COLUMN_NAME = A.COLUMN_NAME        
                AND E.OWNER = '[소유자명]'        
                AND D.CONSTRAINT_TYPE = 'P'        
                AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME
                AND ROWNUM = 1) PK,        
             (SELECT DECODE(MAX(CONSTRAINT_TYPE), 'R', 'FK', '')         
              FROM ALL_CONSTRAINTS D,
                   ALL_CONS_COLUMNS E        
              WHERE E.TABLE_NAME = A.TABLE_NAME        
                AND E.COLUMN_NAME = A.COLUMN_NAME        
                AND E.OWNER = '[소유자명]'        
                AND D.CONSTRAINT_TYPE = 'R'        
                AND D.CONSTRAINT_NAME = E.CONSTRAINT_NAME) FK,        
               '' 설명 
     FROM ALL_TAB_COLUMNS A,        
          ALL_COL_COMMENTS B,        
          ALL_TAB_COMMENTS C               
     WHERE A.TABLE_NAME = B.TABLE_NAME        
       AND A.COLUMN_NAME = B.COLUMN_NAME        
       AND A.TABLE_NAME = C.TABLE_NAME
       AND C.COMMENTS IS NOT NULL       
       AND A.OWNER = '[소유자명]')          
  ORDER BY 2, 3;

 

** 테이블 #3

SELECT *
FROM (SELECT C.COMMENTS 테이블한글명,
             A.TABLE_NAME 테이블명,
             A.COLUMN_ID,
             A.COLUMN_NAME 컬럼명,
             B.COMMENTS 컬럼한글명,
             DECODE(A.NULLABLE, 'Y', '', 'Y') NOTNULL여부,
             A.DATA_TYPE 데이터타입,
             DECODE(A.DATA_TYPE, 'NUMBER', DECODE(A.DATA_PRECISION, '', '', A.DATA_PRECISION||DECODE(A.DATA_SCALE, 0, '', ','||A.DATA_SCALE)), A.DATA_LENGTH)  길이
        FROM ALL_TAB_COLUMNS A,
             ALL_COL_COMMENTS B,
             ALL_TAB_COMMENTS C
        WHERE A.TABLE_NAME = B.TABLE_NAME
          AND A.COLUMN_NAME = B.COLUMN_NAME
          AND A.TABLE_NAME = C.TABLE_NAME
          AND C.COMMENTS IS NOT NULL
          AND B.COMMENTS LIKE '%[테이블명]%')
ORDER BY 2, 3;

( [테이블명]에서 LIKE 조건으로 테이블 검색하는 쿼리 )

** 인덱스

SELECT a.INDEX_NAME
     , a.TABLE_NAME
     , b.tablespace_name
     , b.index_type
     , b.uniqueness
     , a.COLUMN_NAME
     , a.COLUMN_POSITION
     , a.DESCEND 
FROM user_ind_columns a, user_indexes b 
WHERE a.table_name LIKE '%' 
AND a.index_name = b.index_name  
ORDER BY a.table_name, a.index_name, a.COLUMN_POSITION; 

 

** 오브젝트

SELECT * 
FROM user_objects 
WHERE object_type in ('VIEW','TRIGGER','FUNCTION','PROCEDURE','SEQUENCE') 

** PK 조회 (constraint_name = null 이면 PK 없음)

SELECT A.TABLE_NAME, 
       C.CONSTRAINT_NAME 
FROM USER_TABLES A, 
     (SELECT * 
      FROM USER_CONSTRAINTS 
      WHERE CONSTRAINT_TYPE='P') C 
WHERE A.TABLE_NAME = C.TABLE_NAME(+); 

** 테이블 목록

SELECT A.TABLE_NAME  
     , B.COMMENTS  
FROM USER_TABLES A  
   , USER_TAB_COMMENTS B  
WHERE 1=1  
AND A.TABLE_NAME = B.TABLE_NAME  
AND A.TABLE_LOCK = 'ENABLED' 
ORDER BY A.TABLE_NAME;