[오라클] 스키마 추출.

with INFO as (
    select 'INMUN2018' as username
         , 'TB_CODE'   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_type || '(' || data_length || ')'
     , 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;