[SQL] 오라클 테이블 레이아웃 만들기 (PK포함)

프로젝트 나올때마다 맨날 새로 만들어서 귀찮아서 걍 블로그에 올리기로 함

select tb.owner
    ,tb.table_name
    ,tb.comments
    ,tc.column_name
    ,tc.comments
    ,tcop.data_type
    ,tcop.data_length
    ,tcop.nullable
    ,tcop.column_id
    ,tcop.data_type
    ,tcop.data_length
    ,(select case when tbpk.position = 1 then 'pk' when tbpk.position > 1 then 'idx' else '' end 
from all_cons_columns tbpk
    where 1=1
    and tbpk.owner like '스키마명%'
    and tbpk.table_name = tb.table_name 
    and tbpk.column_name = tcop.column_name
    and tbpk.constraint_name like 'TB_%' /* 테이블에 적용되는 공통 명명규칙이 있다면 기술 */
    and rownum = 1 ) as pk
from all_tab_comments tb
left join all_col_comments tc
on tb.table_name = tc.table_name
left join all_tabl_col tcop
on tb.table_name = tcop.table_name
and tc.column_name = tcop.column_name
where 1=1
and tb.owner like '계정명'
--and tb.table_name = '테이블명'
order by tb.owner, tb.table_name, tcop.column_id, pk