Sqlserver2005迁移至Oracle系列之一：生成表
/*
--for oracle____oracle
--本脚本将产生当前数据库的表的 脚本。包含约束、列上定义的默认值、索引
--不产生check、外键；udf、规则、默认、同义词、存储空间、安全主体及权限等脚本
--产生的脚本不含构架信息，但可以指定要生成脚本的构架
--表名支持like运算
--将sqlserver中的date数据类型都对应为oracle中的timestamp类型，相应的去掉以前---以date型作为主键一部分的表的不创建约束---的做法
*/
/*
--todo
--在sqlserver的长类型和oracle的长类型对应问题，字符的个数限制不一致！暂时不处理！
*/
set nocount on
declare @tabname sysname --支持通配符
declare @schema sysname --构架（默认:dbo）
declare @isenterprise int --是否支持企业功能（0.no；1.yes）:create index [online];
--create table partition clause;
declare @istbs int --是否生成自定义表空间（0.no；1.yes）,第一次运行时使用
declare @tbspath sysname --自定义表空间的文件路径（默认"d:\"），依赖 @istbs = 1，第一次运行时使用
declare @onlydeltbs int --是否只删除自定义表空间（0.no；1.yes），依赖 @istbs = 1
declare @isuser int --是否生成用户（0.no；1.yes）,第一次运行时使用
declare @user sysname --用户名称（默认"dbo"），依赖于 @isuser = 1
declare @onlydeluser int --是否只删除用户（0.no；1.yes），依赖于 @isuser = 1 and @user
declare @istable int --是否生成表0.no；1.yes）
declare @onlydeltable int --是否只删除表（0.no；1.yes），依赖 @istable = 1
declare @isindex int --是否生成表索引（0.no；1.yes）
declare @onlydelindex int --是否只删除索引（0.no；1.yes），依赖 @isindex = 1
declare @issequence int --是否生成表序列对象（0.no；1.yes）
declare @onlydelseq int --是否只删除序列对象（0.no；1.yes），依赖 @issequence = 1
declare @issynonym int --是否生成表的同义词（0.no；1.yes）
declare @onlydelsyn int --是否只删除同义词（0.no；1.yes），依赖 @issynonym = 1
set @isenterprise = 0
set @schema = 'dbo'
set @tabname = '%'
set @istbs = 0
set @tbspath = 'z:\uc'
set @onlydeltbs = 0
set @isuser = 0
set @user = 'dbo'
set @onlydeluser = 0
set @istable = 1
set @onlydeltable = 0
set @issynonym = 1
set @onlydelsyn = 0
set @isindex = 1
set @onlydelindex = 0
set @issequence = 1
set @onlydelseq = 0
set @tbspath = ltrim(rtrim(@tbspath))
if right(@tbspath,1)<>'\'
set @tbspath = @tbspath + '\'
--清理临时表
begin
--生成临时表对象，含有表的元数据
if not object_id('tempdb..##table') is null
drop table ##table
create table ##table(ident int default 1,ctext varchar(4000) null default '')
if not object_id('tempdb..#table_define') is null
drop table #table_define
if not object_id('tempdb..#table_constraints') is null
drop table #table_constraints
if not object_id('tempdb..#table_indexes') is null
drop table #table_indexes
end
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
insert into ##table(ident,ctext) select -1,'--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
insert into ##table(ident,ctext) select -1,'BEGIN'
--以下ver1.1
insert into ##table(ident,ctext) select 0,'BEGIN'
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''ALTER
SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS"'';'
insert
into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''ALTER SESSION
SET NLS_TIMESTAMP_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF3"'';'
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR'';'
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_SORT = BINARY_CI'';'
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_COMP = LINGUISTIC'';'
--insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '''';'
insert into ##table(ident,ctext) select 0,'END;'
--以上ver1.1
end
if @istbs = 1
begin
--创建默认用户表空间（大文件）
if @onlydeltbs = 1
insert into ##table(ident,ctext) select 1,'--删除默认用户表空间,设置system为默认用户表空间'
else
insert into ##table(ident,ctext) select 1,'--创建默认用户表空间'
insert into ##table(ident,ctext) select 1,'DECLARE'
insert into ##table(ident,ctext) select 2,'DMSQL VARCHAR2(512):='''';'
insert into ##table(ident,ctext) select 2,'EXIST INT:=0;'
insert into ##table(ident,ctext) select 1,'BEGIN'
insert into ##table(ident,ctext) select 2,'SELECT COUNT(1) INTO EXIST
FROM V$TABLESPACE WHERE NAME = ''USER_DEFAULT_SPACE'';'
if @onlydeltbs = 1
begin
insert into ##table(ident,ctext) select 2,'IF EXIST = 1 THEN'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE ''ALTER DATABASE DEFAULT TABLESPACE SYSTEM'';'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE ''DROP
TABLESPACE USER_DEFAULT_SPACE INCLUDING CONTENTS AND DATAFILES'';'
end
else
begin
insert into ##table(ident,ctext) select 2,'IF EXIST = 0 THEN'
insert into ##table(ident,ctext) select 3,'DMSQL := ''CREATE
BIGFILE TABLESPACE USER_DEFAULT_SPACE DATAFILE ''''' + @tbspath +
'user_default_space.dbf'''' SIZE 10m REUSE AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE'';'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE DMSQL;'
insert into ##table(ident,ctext) select 3,'--设置为默认用户表空间'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE ''ALTER DATABASE DEFAULT TABLESPACE USER_DEFAULT_SPACE'';'
end
insert into ##table(ident,ctext) select 2,'END IF;'
insert into ##table(ident,ctext) select 1,'END;'
insert into ##table(ident,ctext) select 1,''
--创建32个分区表空间
if @onlydeltbs = 1
insert into ##table(ident,ctext) select 1,'--删除32个分区表空间'
else
insert into ##table(ident,ctext) select 1,'--创建32个分区表空间'
insert into ##table(ident,ctext) select 1,'DECLARE'
insert into ##table(ident,ctext) select 2,'DMSQL VARCHAR2(512):='''';'
insert into ##table(ident,ctext) select 2,'EXIST INT:=0;'
insert into ##table(ident,ctext) select 1,'BEGIN'
insert into ##table(ident,ctext) select 2,'FOR i IN 1..32 LOOP'
insert into ##table(ident,ctext) select 3,'SELECT COUNT(1) INTO EXIST
FROM V$TABLESPACE WHERE NAME = ''USER_'' || lpad(to_char(i),2,''0'');'
if @onlydeltbs = 1
begin
insert into ##table(ident,ctext) select 3,'IF EXIST = 1 THEN'
insert into ##table(ident,ctext) select 4,'DMSQL := ''DROP
TABLESPACE user_'' || lpad(to_char(i),2,''0'') ||'' INCLUDING CONTENTS
AND DATAFILES'';'
end
else
begin
insert into ##table(ident,ctext) select 3,'IF EXIST = 0 THEN'
insert into ##table(ident,ctext) select 4,'DMSQL := ''CREATE
TABLESPACE user_'' || lpad(to_char(i),2,''0'') ||'' DATAFILE ''''' +
@tbspath + 'user_'' || lpad(to_char(i),2,''0'') || ''.dbf'''' SIZE 1M
REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
AUTOALLOCATE'';'
end
insert into ##table(ident,ctext) select 4,'EXECUTE IMMEDIATE DMSQL;'
insert into ##table(ident,ctext) select 4,'EXIST:=0;'
insert into ##table(ident,ctext) select 3,'END IF;'
insert into ##table(ident,ctext) select 2,'END LOOP;'
insert into ##table(ident,ctext) select 1,'END;'
end
--创建用户
if @isuser = 1
begin
if @onlydeltbs = 1
insert into ##table(ident,ctext) select 1,'--删除用户[' + @user + ']'
else
insert into ##table(ident,ctext) select 1,'--创建用户[' + @user + ']'
insert into ##table(ident,ctext) select 1,'DECLARE'
insert into ##table(ident,ctext) select 2,'DMSQL VARCHAR2(512):='''';'
insert into ##table(ident,ctext) select 2,'EXIST INT:=0;'
insert into ##table(ident,ctext) select 1,'BEGIN'
insert into ##table(ident,ctext) select 2,'SELECT COUNT(1) INTO EXIST
FROM Dba_Users WHERE username = upper(''' + @user + ''');'
if @onlydeltbs = 1
begin
insert into ##table(ident,ctext) select 2,'IF EXIST = 1 THEN'
insert into ##table(ident,ctext) select 3,'DMSQL := ''DROP USER ' + @user + ' CASCADE'';'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE DMSQL;'
end
else
begin
insert into ##table(ident,ctext) select 2,'IF EXIST = 0 THEN'
insert into ##table(ident,ctext) select 3,'DMSQL := ''CREATE USER ' + @user + ' IDENTIFIED BY ' + @user + ''';'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE DMSQL;'
insert into ##table(ident,ctext) select 3,'DMSQL := ''GRANT DBA,UNLIMITED TABLESPACE TO ' + @user + ''';'
insert into ##table(ident,ctext) select 3,'EXECUTE IMMEDIATE DMSQL;'
end
insert into ##table(ident,ctext) select 2,'END IF;'
insert into ##table(ident,ctext) select 1,'END;'
insert into ##table(ident,ctext) select 1,''
end
if (@istable = 0) and (@issynonym = 0) and (@isindex = 0) and (@issequence = 0)
set @istable = @istable
else
begin
begin
--生成表的基本信息，包含（列名称、类型、长度、精度；是否为null；字段的默认值（约束）；是否是自增列、基数、增量）
select a.name as tabname,b.name as colname,(select name from
sys.types where user_type_id = c.system_type_id) as
typename,b.max_length as length,b.precision,b.scale,b.is_nullable as
isnull
,d.name as defname,d.definition as 'default'
,b.is_identity as isidentity,f.seed_value as seed,f.increment_value as incre
,e.value as describe
into #table_define
from sys.objects a
inner join sys.columns b on a.object_id = b.object_id
inner join sys.types c on b.user_type_id = c.user_type_id
left join sys.default_constraints d on a.object_id = d.parent_object_id and b.column_id = d.parent_column_id
left join sys.extended_properties e on e.class = 1 and a.object_id = e.major_id and b.column_id = e.minor_id
left join sys.identity_columns f on a.object_id = f.object_id and b.column_id = f.column_id
where (a.is_ms_shipped = 0) and (a.type = 'U') and (a.name
<> 'sysdiagrams') and (a.name like @tabname) and (a.schema_id =
schema_id(@schema))
order by a.name,b.column_id
--生成表的主键、唯一键约束
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
--定义临时变量
begin
declare @tablename varchar(256)
declare @colname varchar(256)
declare @typename varchar(256)
declare @oratypename varchar(256)
declare @length varchar(256)
declare @precision int
declare @scale int
declare @isnull int
declare @defname varchar(256)
declare @default varchar(256)
declare @isidentity int
declare @seed sql_variant
declare @incre sql_variant
declare @describe varchar(256)
declare @sequence sysname
declare @sequence_is int --是否存在自增列
declare @sequence_colname sysname
declare @sequence_seed bigint
declare @sequence_incre bigint
declare @type varchar(256)
declare @keyname varchar(256)
declare @isclusted int
declare @isdesc varchar(256)
declare @indname varchar(256)
declare @isunique int
declare @ignore_dup_key int
declare @col_define varchar(4000)
declare @exiests_constraints int
declare @constraint_define varchar(4000)
declare @col_list varchar(4000)
declare @index_define varchar(4000)
declare @synonyms sysname
declare @pos int
end
--表名称游标
declare cr_table_name cursor for
select distinct tabname from #table_define
open cr_table_name
fetch next from cr_table_name into @tablename
if @@fetch_status = 0
begin
--PL/SQL块头部定义
insert into ##table(ident,ctext) select 0,'DECLARE'
insert into ##table(ident,ctext) select 1,'VA_EXIST_TABLE INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_SEQUENCE INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_INDEX INT:=0;'
insert into ##table(ident,ctext) select 1,'VA_EXIST_SYNONYMS INT:=0;'
insert into ##table(ident,ctext) select 0,'BEGIN'
end
else
begin
close cr_table_name
deallocate cr_table_name
print '没有表对象需要生成！'
return
end
while @@fetch_status = 0
begin
insert into ##table(ident,ctext) select -1,'--' +
replicate('-',30) + quotename(upper(@tablename),'"') + ' @ ' +
quotename(convert(varchar(19),getdate(),121)) + replicate('-',20)
--生成或删除表
if @istable = 1
begin
--表头定义,删除部分
if @onlydeltable = 1
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除表表'
else
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(*)
INTO VA_EXIST_TABLE FROM ALL_TABLES WHERE OWNER = ''' + upper(@user) +
''' AND TABLE_NAME = ' + quotename(upper(@tablename),'''') + ';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_TABLE > 0 THEN '
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'DROP
TABLE ' + quotename(upper(@user),'"') + '.' +
quotename(upper(@tablename),'"') + ' CASCADE CONSTRAINTS PURGE'';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_TABLE := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
--表头定义,创建部分,如果不只是删除
if @onlydeltable = 0
begin
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' +
'CREATE TABLE ' + quotename(upper(@user),'"') + '.' +
quotename(upper(@tablename),'"')
insert into ##table(ident,ctext) select 2,'('
--根据是否存在表约束来决定列定义后是否跟随‘，’
if exists( select *
from #table_constraints
where (tabname = @tablename)
--and (@tablename not like 'plat_reglog%') --ver1.1
--and (@tablename <> 'PLAT_RegUpgrade') --ver1.1
)
set @exiests_constraints = 1
else
set @exiests_constraints = 0
--表列定义
begin
set @sequence_is = 0
declare cr_table_column cursor for
select colname,typename,length,[precision],scale,[isnull],[default],isidentity,seed,incre
from #table_define
where tabname = @tablename
open cr_table_column
fetch next from cr_table_column into
@colname,@typename,@length,@precision,@scale,@isnull,@default,@isidentity,@seed,@incre
while @@fetch_status = 0
begin
--列明细
begin
set @col_define = ''
set @colname = @colname
set @length = case
when (@typename
in('char','nchar','varchar','nvarchar','binary','varbinary','sysname'))
and (@length > 0) then @length
when (@typename
in('char','nchar','varchar','nvarchar','binary','varbinary')) and
(@length = -1) then '-1' --MAX
else null
end
set @precision = case
when @typename
in('bigint','smallmoney','money','decimal','numeric','real','float')
then @precision
else null
end
set @scale = case
when @typename
in('bigint','smallmoney','money','decimal','numeric','real','float')
then @scale
else null
end
set @oratypename =
(case @typename
when 'numeric' then 'numeric'
when 'image' then 'blob'
when 'datetime' then 'timestamp'
when 'smalldatetime' then 'timestamp'
when 'real' then 'float'
when 'decimal' then 'numeric'
when 'xml' then 'xml'
when 'timestamp' then 'timestamp'
when 'char' then 'char'
when 'nchar' then 'nchar'
when 'varchar' then 'varchar2'
when 'nvarchar' then 'nvarchar2'
when 'sysname' then 'nvarchar2'
when 'uniqueidentifier' then 'rowid'
when 'float' then 'float'
when 'bit' then 'int'
when 'sql_variant' then 'nvarchar2' --sql_variant
when 'int' then 'int'
when 'bigint' then 'numeric'
when 'smallint' then 'int'
when 'tinyint' then 'int'
when 'text' then 'clob'
when 'ntext' then 'nclob'
when 'money' then 'numeric'
when 'smallmoney' then 'numeric'
when 'binary' then 'raw'
when 'varbinary' then 'raw'
end)
set @oratypename =
(case
when (@typename in
('binary','varbinary')) and ((@length = -1) OR (@length > 2000))
then 'long raw'
when (@typename in ('char','nchar')) and ((@length = -1) OR
(@length > 2000)) then 'long'
when (@typename in ('varchar','nvarchar')) and
((@length = -1) OR (@length > 4000)) then 'long'
else @oratypename
end)
set @length = case @oratypename
when 'long' then null
when 'long raw' then null
else @length
end
set @default = case
when @default like
'((%' then right(left(@default,len(@default)-2),len(@default)-4)
when @default like
'(''%' then '''' +
right(left(@default,len(@default)-1),len(@default)-2) + ''''
else null
end
set @col_define = replicate(' ',4) +
quotename(upper(@colname),'"') + replicate(' ',4) + upper(@oratypename)
+ isnull(('(' + cast(@length as varchar(50)) + ')'),'')
+ isnull('(' + cast(@precision
as varchar(50)) + isnull(',' + cast(@scale as varchar(50)),'') +
')','')
+ replicate(' ',4) + isnull('DEFAULT ' + @default,'')
+ replicate(' ',4) + (case @isnull when 1 then '' else 'NOT NULL' end)
set @col_define = rtrim(@col_define)
if @isidentity = 1
begin
set @sequence_is = 1
set @sequence_colname = @colname
set @sequence_seed = cast(@seed as bigint)
set @sequence_incre = cast(@incre as bigint)
end
end
--列尾部
begin
fetch next from cr_table_column into
@colname,@typename,@length,@precision,@scale,@isnull,@default,@isidentity,@seed,@incre
if @@fetch_status = 0
set @col_define = @col_define + ','
else if @exiests_constraints = 1
set @col_define = @col_define + ','
insert into ##table(ident,ctext) select 3,@col_define
end
end
close cr_table_column
deallocate cr_table_column
end
--主键、唯一键约束定义
if @exiests_constraints = 1
begin
declare cr_constraints_name cursor for
select distinct keyname,[type],isclusted
from #table_constraints
where (tabname = @tablename)
open cr_constraints_name
fetch next from cr_constraints_name into @keyname,@type,@isclusted
if @@fetch_status = 0
--定义约束
while @@fetch_status = 0
begin
--约束头部
set @constraint_define = 'CONSTRAINT ' + quotename(left(upper(@keyname),30),'"')
+ case @type
when 'PK' then ' PRIMARY KEY '
when 'UQ' then ' UNIQUE '
else ' '
end
+ '('
--约束列
begin
set @col_list = ''
declare cr_constraints cursor for
select colname,isdesc
from #table_constraints
where (tabname = @tablename) and (keyname = @keyname)
open cr_constraints
fetch next from cr_constraints into @colname,@isdesc
while @@fetch_status = 0
begin
set @col_list = @col_list + quotename(upper(@colname),'"')
--是否有下一个约束，决定是否加上‘,’
fetch next from cr_constraints into @colname,@isdesc
if @@fetch_status = 0
set @col_list = @col_list + ','
end
close cr_constraints
deallocate cr_constraints
end
--约束尾部
set @constraint_define = @constraint_define + @col_list + ')'
--约束列游标下移
fetch next from cr_constraints_name into @keyname,@type,@isclusted
--是否有下一个约束，决定是否加上‘,’
if @@fetch_status = 0
set @constraint_define = @constraint_define + ','
insert into ##table(ident,ctext) select 3,@constraint_define
end
--表约束游标下移
close cr_constraints_name
deallocate cr_constraints_name
end
--表尾部
begin
declare @partitioncolumn sysname
set @partitioncolumn = null
if @tablename in
('plat_uidlist','plat_feeinfo','plat_uidinfo','plat_reginfo','plat_freeuid','plat_linkgroup','plat_linkman','plat_reglog','plat_reglog_2007','plat_reglog_2008'
)
set @partitioncolumn = '"UID"'
else if @tablename in
('plat_phoneinfo','plat_phonelimited','plat_phoneseglimited','plat_freephone','plat_areaphonelist')
set @partitioncolumn = 'PHONE'
else if @tablename in
('sms_recdmsgret','sms_recdmsgret_2007','sms_recdmsgret_2008','sms_sentmsgret','sms_sentmsgret_2007','sms_sentmsgret_2008')
set @partitioncolumn = 'CHANNO'
if (not @partitioncolumn is null) and (@isenterprise = 1)
begin
begin
insert into ##table(ident,ctext) select 2,')'
insert into ##table(ident,ctext) select 2,'PARTITION BY HASH(' + @partitioncolumn + ')'
insert into ##table(ident,ctext) select 2,'PARTITIONS 32 STORE IN'
declare @partition varchar(2000)
declare @i int
declare @si varchar(100)
set @partition = ''
set @i = 1
while @i < 32
begin
set @si = 'USER_' + REPLICATE('0',2-LEN(CAST(@i as varchar(2)))) + CAST(@i as varchar(2))
set @i = @i + 1
set @partition = @partition + @si + ','
if @i = 32
begin
set @si = 'USER_' +
REPLICATE('0',2-LEN(CAST(@i as varchar(2)))) + CAST(@i as varchar(2))
set @partition = @partition + @si
end
end
insert into ##table(ident,ctext) select 3,'(' + @partition + ')'
insert into ##table(ident,ctext) select 2,''';'
end
end
else
insert into ##table(ident,ctext) select 2,')'';'
insert into ##table(ident,ctext) select -1,''
end
end
end
if (@issequence = 1)
begin
begin
set @sequence_is = 0
select @sequence_is = isidentity,@sequence_seed =
cast(seed as bigint),@sequence_incre = cast(incre as bigint)
from #table_define
where (tabname = @tablename) and (isidentity = 1)
end
if @sequence_is = 1
begin
set @sequence = left(upper(@tablename + '_ID'),30)
--先删除（清理）
if @onlydelseq = 1
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的序列'
else
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的序列'
insert into ##table(ident,ctext) select 1,'SELECT
COUNT(1) INTO VA_EXIST_SEQUENCE FROM ALL_SEQUENCES WHERE
SEQUENCE_OWNER = ''' + upper(@user) + ''' AND SEQUENCE_NAME = ' +
quotename(@sequence,'''') + ';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_SEQUENCE > 0 THEN '
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + 'DROP
SEQUENCE ' + quotename(upper(@user),'"') + '.' +
quotename(@sequence,'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_SEQUENCE := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
--创建序列
if @onlydelseq = 0
begin
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' +
'CREATE SEQUENCE ' + quotename(upper(@user),'"') + '.' +
quotename(@sequence,'"')
insert into
##table(ident,ctext) select 4,'START WITH 100000000' -- +
cast(@sequence_seed as varchar(50)) --ver1.1
insert into ##table(ident,ctext) select 4,'INCREMENT BY ' + cast(@sequence_incre as varchar(50))
insert into ##table(ident,ctext) select 4,'NOMAXVALUE'
insert into ##table(ident,ctext) select 4,'NOCYCLE'
insert into ##table(ident,ctext) select 4,'NOCACHE'
insert into ##table(ident,ctext) select 4,'NOORDER'
insert into ##table(ident,ctext) select 2,''';'
insert into ##table(ident,ctext) select -1,''
end
end
end
--创建索引
if @isindex = 1
begin
declare cr_index_name cursor for
select distinct indname,isclusted,isunique,ignore_dup_key
from #table_indexes
where tabname = @tablename
open cr_index_name
fetch next from cr_index_name into @indname,@isclusted,@isunique,@ignore_dup_key
while @@fetch_status = 0
begin
--删除索引部分
if @onlydelindex = 1
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的索引'
else
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的索引'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(1) INTO VA_EXIST_INDEX'
insert into ##table(ident,ctext) select 1,'FROM
DBA_INDEXES A LEFT JOIN ALL_CONSTRAINTS B ON A.INDEX_NAME =
B.CONSTRAINT_NAME'
insert into
##table(ident,ctext) select 1,'WHERE (A.OWNER = '''+ upper(@user) +
''') AND (B.CONSTRAINT_NAME IS NULL) AND A.INDEX_NAME = ''' +
left(upper(@indname),30) + ''';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_INDEX > 0 THEN '
insert into ##table(ident,ctext) select 2,'EXECUTE
IMMEDIATE ''DROP INDEX ' + quotename(upper(@user),'"') + '.' +
quotename(left(upper(@indname),30),'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_INDEX := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
--创建索引部分，如果不只是删除索引
if @onlydelindex = 0
begin
--index头部
set @index_define = 'CREATE '
+ case @isunique
when 1 then 'UNIQUE '
else ''
end
+ 'INDEX ' +
quotename(upper(@user),'"') + '.' +
quotename(left(upper(@indname),30),'"')
insert into ##table(ident,ctext) select 1,'EXECUTE IMMEDIATE '
insert into ##table(ident,ctext) select 2,'''' + @index_define
--index列明细
begin
set @index_define = 'ON ' +
quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"') +
'('
set @col_list = ''
declare cr_index cursor for
select colname,isdesc
from #table_indexes
where (tabname = @tablename) and (indname = @indname)
open cr_index
fetch next from cr_index into @colname,@isdesc
while @@fetch_status = 0
begin
set @col_list = @col_list + quotename(upper(@colname),'"')
fetch next from cr_index into @colname,@isdesc
if @@fetch_status = 0
set @col_list = @col_list + ' ' + @isdesc + ','
end
close cr_index
deallocate cr_index
end
--index尾部
set @index_define = @index_define + @col_list + ')'
if @tablename in(
'plat_uidlist','plat_feeinfo','plat_uidinfo','plat_reginfo','plat_freeuid','plat_linkgroup','plat_linkman','plat_reglog','plat_reglog_2007','plat_reglog_2008',
'plat_phoneinfo','plat_phonelimited','plat_phoneseglimited','plat_freephone','plat_areaphonelist',
'sms_recdmsgret','sms_recdmsgret_2007','sms_recdmsgret_2008','sms_sentmsgret','sms_sentmsgret_2007','sms_sentmsgret_2008'
)
if @isenterprise = 1
insert into ##table(ident,ctext) select 3,@index_define + 'ONLINE NOLOGGING LOCAL'';'
else
insert into ##table(ident,ctext) select 3,@index_define + 'NOLOGGING'';'
else
if @isenterprise = 1
insert into ##table(ident,ctext) select 3,@index_define + 'ONLINE NOLOGGING'';'
else
insert into ##table(ident,ctext) select 3,@index_define + 'NOLOGGING'';'
end
fetch next from cr_index_name into @indname,@isclusted,@isunique,@ignore_dup_key
insert into ##table(ident,ctext) select 1,''
end
close cr_index_name
deallocate cr_index_name
end
--创建表的public同义词，去掉‘_’字符前面的部分
if @issynonym = 1
begin
set @pos = charindex('_',@tablename)
if @pos > 0 and @pos < len(@tablename)
begin
set @synonyms = upper(substring(@tablename,@pos +1,256))
--删除已存在的同义词
if @onlydelsyn = 1
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '删除该表的同义词'
insert into ##table(ident,ctext) select 1,'SELECT COUNT(1) INTO VA_EXIST_SYNONYMS FROM ALL_SYNONYMS'
insert into ##table(ident,ctext) select
1,'WHERE OWNER = '''+ upper(@user) + ''' AND SYNONYM_NAME = ''' +
@synonyms + ''';'
insert into ##table(ident,ctext) select 1,'IF VA_EXIST_SYNONYMS > 0 THEN '
insert into ##table(ident,ctext) select
1,'EXECUTE IMMEDIATE ''DROP SYNONYM ' + quotename(upper(@user),'"') +
'.' + quotename(@synonyms,'"') + ''';'
insert into ##table(ident,ctext) select 2,'VA_EXIST_SYNONYMS := 0;'
insert into ##table(ident,ctext) select 1,'END IF;'
insert into ##table(ident,ctext) select -1,''
end
else
begin
insert into ##table(ident,ctext) select -1,'--' + replicate('*',40) + '创建该表的同义词'
insert into ##table(ident,ctext) select 1,'EXECUTE
IMMEDIATE ''CREATE OR REPLACE SYNONYM ' + quotename(upper(@user),'"')
+ '.' + quotename(@synonyms,'"') + ' FOR ' +
quotename(upper(@user),'"') + '.' + quotename(upper(@tablename),'"') +
''';'
insert into ##table(ident,ctext) select -1,''
end
end
end
--表游标下移
fetch next from cr_table_name into @tablename
end
close cr_table_name
deallocate cr_table_name
insert into ##table(ident,ctext) select 0,'END;'
end
--PL/SQL块尾部定义
insert into ##table(ident,ctext) select -1,'END;'
insert into ##table(ident,ctext) select -1,'--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
insert into ##table(ident,ctext) select -1,'--' + replicate('*',50) + '该脚本适用于 oracle数据库系统' + replicate('*',50)
--清理临时表
begin
if not object_id('tempdb..#table_define') is null
drop table #table_define
if not object_id('tempdb..#table_constraints') is null
drop table #table_constraints
if not object_id('tempdb..#table_indexes') is null
drop table #table_indexes
end
--返回结果集
select replicate(' ',(ident + 1)*4) + ctext
from ##table
