[Oracle] 테이블 정의서 추출 쿼리

Oracle 상에서 테이블의 정의서를 뽑는 쿼리이다.

테이블 컬럼의 타입, Null 가능 여부, Primary Key, Forien Key 여부, Comment 등을 확인 할 수 있다.

select a.owner,
       a.table_name,
       a.column_name,
       a.data_type,
       a.nullable,
       case
           when b.constraint_type='P' then 'O'
           else 'X'
       end as PK,
       case
           when b.constraint_type='R' then 'O'
           else 'X'
       end as FK,
       c.comments
  from dba_tab_columns a,
       (select a.owner,
               a.table_name,
               a.constraint_name,
               a.constraint_type,
               b.column_name
          from dba_constraints a,
               dba_cons_columns b
         where 1=1
           and a.owner = b.owner
           and a.constraint_name = b.constraint_name
           and a.owner = 'username'
           and a.constraint_type in ('P',
                       'R')) b ,
       dba_col_comments c
 where 1=1
   and a.owner = b.owner
   and a.table_name = b.table_name
   and a.column_name = b.column_name
   and a.owner = c.owner
   and a.table_name =c.table_name
   and a.column_name = c.column_name ;