오라클 DDL 생성 스트립트

oracle ddl 생성스크립트 -- 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

오라클 DDL 생성 스트립트

DB CAFE