Sqlserver2005迁移至Oracle系列之一：生成表(上)

核心提示： --生成表的主键、唯一键约束 select object_name(a.parent_object_id) as tabname,a.type,a.name as keyname,(case b.type when 1 then 1 else 0 end) as isclusted ,c

--生成表的主键、唯一键约束

　　　　　　　 select object_name(a.parent_object_id) as tabname,a.type,a.name as keyname,(case b.type when 1 then 1 else 0 end) as isclusted
　　　　　　　　　　　 ,col_name(c.object_id,c.column_id) as colname,(case c.is_descending_key when '0' then 'ASC' else 'DESC' end)as isdesc
　　　　　　　 into #table_constraints
　　　　　　　 from sys.key_constraints a
　　　　　　　　　　　 inner join sys.indexes b on a.parent_object_id = b.object_id and a.unique_index_id = b.index_id
　　　　　　　　　　　 inner join sys.index_columns c on b.object_id = c.object_id and b.index_id = c.index_id
　　　　　　　 where (object_name(a.parent_object_id) like @tabname) and (a.schema_id = schema_id(@schema))
　　　　　　　 order by object_name(a.parent_object_id),a.name,c.index_id,c.key_ordinal

--生成表的一般index信息

　　　　　　　 select b.name as tabname,'IN' as type,a.name as indname,a.type as isclusted,a.is_unique as isunique
　　　　　　　　　　　 ,a.ignore_dup_key,c.is_included_column,a.fill_factor,a.is_padded,a.is_disabled,a.allow_row_locks,a.allow_page_locks
　　　　　　　　　　　 ,d.name as colname,(case c.is_descending_key when '0' then 'ASC' else 'DESC' end)as isdesc
　　　　　　　 into #table_indexes
　　　　　　　 from sys.indexes a
　　　　　　　　　　　 inner join sys.objects b on a.object_id = b.object_id and b.is_ms_shipped = 0 and b.type = 'U' and a.name is not null
　　　　　　　　　　　 inner join sys.index_columns c on a.object_id = c.object_id and a.index_id = c.index_id
　　　　　　　　　　　 inner join sys.columns d on a.object_id = d.object_id and c.column_id = d.column_id
　　　　　　　 where (not (a.is_primary_key = 1 or a.is_unique_constraint = 1)) and (b.name like @tabname) and (b.schema_id = schema_id(@schema))
　　　　　　　 order by b.name,a.index_id,c.key_ordinal
　　　 end