오라클 테이블 스키마 가져오는 쿼리

오라클 테이블 스키마 가져오는 쿼리

오라클 테이블 스키마를 가져와 테이블 스키마 정의서를 만들 때 사용하던 쿼리를 소개한다.

오라클 사용자와 테이블명에 대한 PK, FK, Column name, Data type, Data length, Nullable, data default, Comments 등의 정보를 가져오는 쿼리이다.

	
 
-- show schema of a table
with INFO as (
    select 
         'USER_NAME' as username
        ,'ADM_USER' as usertable
    from dual
),
PK as (
select 
     c.column_name
    ,case when a.constraint_type = 'R' then 'Y' else '' end as column_fk 
from sys.user_cons_columns c, sys.user_constraints a, INFO u
where a.owner = c.owner
  and a.table_name = c.table_name 
  and a.constraint_name = c.constraint_name 
  and a.constraint_type in ('P','R')
  and a.table_name = u.usertable  
  and a.OWNER = u.username
)
select
     PK
    ,FK
    ,column_name
    ,data_type
    ,data_length
    ,nullable
    ,data_default
    ,comments 
from (
    SELECT 
         case when length(p.column_name) > 0 then 'PK' else '   ' end as PK
        ,p.column_fk as FK
        ,c.column_name
        ,c.data_type
        ,case when c.data_type = 'VARCHAR2' then to_char(data_length)
              when c.data_type = 'DATE' or c.data_type = 'FLOAT' or c.data_type = 'INTEGER' or c.data_type = 'BLOB' then ''
              when c.data_type = 'NUMBER' then (case when data_precision is null then ''
                                                     when data_scale = 0 then to_char(data_precision)
                                                     else to_char(data_precision) || ',' || to_char(data_scale) end
                                                )
              else (case when data_precision is null then ''
                         when data_scale = 0 then to_char(data_precision)
                         else to_char(data_precision) || ',' || to_char(data_scale) end
                   ) end as data_length
        ,c.COLUMN_ID
        ,c.NULLABLE
        ,c.data_default
        ,s.comments
    FROM USER_TAB_COLUMNS c, PK p, INFO u, USER_COL_COMMENTS s
    WHERE c.TABLE_NAME = u.usertable
      and c.column_name = p.column_name(+)
      and c.table_name = s.table_name
      and c.COLUMN_NAME = s.column_name
    )
order by column_id 
	

아래는 오라클의 테이블(Table), 뷰(View), 패키지(Package), 인덱스(Index), 프로시저(Procedure), 펑션(Function), 트리거(Trigger) 와 같은 오라클 오브젝트에 대한 데이터베이스 정보를 가져오는 방법에 대해서 소개한다.

Tables

오라클 테이블스페이별 테이블명 가져오는 쿼리

This is a query to get all Oracle tables for the tablespace

	
 
select 
    * --table_name, ... 
from user_tables
where tablespace_name = 'TABLESPACE_NAME'
order by table_name 
	

Views

오라클 사용자별 뷰(View) 가져오는 쿼리

This is a query to get all Oracle views for the current user

	
 
select 
    * --VIEW_NAME, OWNER, ... 
from ALL_VIEWS 
  and OWNER = 'OWNER_NAME' 
	

Packages

오라클 사용자별 패키지(Packages) 가져오는 쿼리

This is a query to get all Oracle packages for the current user

	
 
select 
    * --OBJECT_NAME, OWNER, ... 
from ALL_OBJECTS 
where upper(OBJECT_TYPE) = 'PACKAGE'
  and OWNER = 'OWNER_NAME'  
	

Procedures

오라클 사용자별 프로시저(Procedure) 가져오는 쿼리

This is a query to get all Oracle procedures for the current user.

	
 
select 
    * --OBJECT_NAME, OWNER, ... 
from ALL_OBJECTS 
where upper(OBJECT_TYPE) = 'PROCEDURE'
  and OWNER = 'OWNER_NAME'  
	

Procedure Columns

오라클 프로시저 내 컬럼 가져오는 쿼리

This is a query to get the columns in an Oracle procedure

	
 
select 
    * --OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT, ... 
from ALL_ARGUMENTS 
order by OWNER, OBJECT_NAME, SEQUENCE 
	

Functions

오라클 사용자별 펑션(Function) 가져오는 쿼리

This is a query to get all Oracle functions for the current user

	
 
select 
    * --OBJECT_NAME, OWNER, ... 
from ALL_OBJECTS 
where upper(OBJECT_TYPE) = upper('FUNCTION') 
  and OWNER = 'OWNER_NAME' 

Triggers

오라클 사용자별 트리거(Trigger) 가져오는 쿼리

This is a query to get all Oracle triggers for the current user

	
 
select 
    * --TRIGGER_NAME, OWNER, ... 
from ALL_TRIGGERS
where OWNER = 'OWNER_NAME' 

Indexes

오라클 사용자별 인덱스(Index) 가져오는 쿼리

This is a query to get all Oracle indexes for the current user

	
 
select
    * --INDEX_NAME, TABLE_NAME, TABLE_OWNER, ...
from ALL_INDEXES
where OWNER = 'OWNER_NAME' 



2014/08/12 - [Note/Database] - 오라클(Oracle) 년도별 주차 계산, 월별 주차 구하기, week of year, week of month

2013/12/03 - [Note/Database] - 오라클 Tablespace 생성, User 생성 방법

2013/10/25 - [Note/Database] - Oracle 오라클 소수점 앞에 0 붙이기