ORACLE匯出表結構指令碼

標籤：

select  case when r = 1 then (select s.COMMENTS from user_tab_comments s where Table_Name= aa.table_name) else ‘‘ end as table_comments,  case when r = 1 then aa.table_name else ‘‘ end as table_name,  aa.r as column_sequence,  aa.COLUMN_NAME,  aa.DATA_TYPE,  aa.data_length,  (select ‘Y‘from user_cons_columns cu, user_constraints auwhere cu.constraint_name = au.constraint_name and au.constraint_type = ‘P‘   and au.table_name = aa.table_name  and cu.COLUMN_NAME = aa.COLUMN_NAME) as IS_PK,  aa.NULLABLE,  aa.DATA_DEFAULT,  aa.column_commentsfrom(select  row_number() over(partition by t.OBJECT_NAME order by t.OBJECT_NAME) r,        t.OBJECT_NAME as table_name,        c.COLUMN_NAME,        c.DATA_TYPE,        case when c.NULLABLE = ‘N‘ then ‘NOT NULL‘ else ‘‘ end as NULLABLE,        case when c.DATA_TYPE in (‘NVARCHAR2‘,‘CHAR‘,‘VARCHAR2‘) then to_char(c.CHAR_LENGTH)             when c.DATA_TYPE in (‘LONG‘,‘FLOAT‘,‘CLOB‘,‘BLOB‘,‘DATE‘)then ‘ ‘             when c.DATA_TYPE = ‘NUMBER‘ then                case when c.DATA_PRECISION is null and c.DATA_SCALE = 0 then                 ‘INTEGER‘                 else                 rtrim(c.DATA_PRECISION ||‘,‘|| c.DATA_SCALE,‘,‘)                 end             else ‘ ‘ end as data_length,        cc.COMMENTS as column_comments,        c.DATA_DEFAULTfrom user_objects t,     user_tab_columns  c,     user_col_comments ccwhere t.OBJECT_TYPE = ‘TABLE‘   and c.TABLE_NAME = t.OBJECT_NAME  and cc.TABLE_NAME = c.TABLE_NAME  and cc.COLUMN_NAME = c.COLUMN_NAME) aa;

 

ORACLE匯出表結構指令碼