Sql server 2005遷移至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 WNER = ''' + 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 WNER = '''+ 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
原文地址：http://www.cnblogs.com/jinzhenshui/articles/1360207.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-598134/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MSSQL Server 遷移至 ORACLE解決方案SQLServerOracle
- SQL Server 遷移至MySQL 關鍵步驟的梳理總結ServerMySql
- sql server 2005資料庫快照SQLServer資料庫
- 已安裝 SQL Server 2005 Express 工具。若要繼續，請刪除 SQL Server 2005 Express 工具SQLServerExpress
- SQL Server 2005效能調整一(zt)SQLServer
- SQL Server 2005效能調整二(zt)SQLServer
- sql server 2005 資料修改的內部原理SQLServer
- 【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19CTTSOracle
- SQL Server 2005詳細安裝過程及配置SQLServer
- 如何建立和還原SQL Server 2005資料庫？SQLServer資料庫
- SQL Server2005使用CTE實現遞迴QCSQLServer遞迴
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- [譯] 系列教程：如何將程式碼遷移至 TensorFlow 1.0
- SQL Server資料庫遷移SQLServer資料庫
- SQL Server 2005的複製儲存過程選項BYSQLServer儲存過程
- Oracle資料庫遷移至PolarDb(阿里雲資料庫)Oracle資料庫阿里
- SQL Server 批量生成資料庫內多個表的表結構SQLServer資料庫
- Hadoop Hive遷移至MaxComputeHadoopHive
- Q&A：在SQL Server 2005中編寫儲存過程RVSQLServer儲存過程
- Oracle RAC遷移至南大通用GBase 8c 解決方案Oracle
- 【RMAN】Oracle使用rman將11.2.0.4資料庫遷移至Oracle12c命令參考Oracle資料庫
- SQL SERVER之分割槽表SQLServer
- SQL server 修改表資料SQLServer
- 揭秘UGO SQL稽核功能4大特性，讓業務平滑遷移至GaussDBGoSQL
- ESXI 遷移至KVM (V2V遷移)
- 透過MySQL Workbench 將 SQL Server 遷移到GreatSQLMySqlServer
- 本部落格已遷移至Wordpress~
- 輕鬆上雲系列之二：其他雲資料遷移至阿里雲阿里
- SQL Server映象自動生成指令碼方法SQLServer指令碼
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- Oracle\MS SQL Server Update多表關聯更新OracleSQLServer
- MySql/Oracle和SQL Server的分頁查MySqlOracleServer
- SQL Server AlwaysOn的Oracle等價技術SQLServerOracle
- Microsoft SQL Server 遷移利器，Babelfish for Aurora PostgreSQL 上線！ROSSQLServerBabel
- [翻譯]SQL Server 2005 Analysis Services效能指南 Part 1 - 理解查詢構架SQLServer
- SQL Server 2000/2005/2008刪除或壓縮資料庫日誌的方法SQLServer資料庫
- 應用遷移至 Android P 操作指南Android
- 記一次簡單的Oracle離線資料遷移至TiDB過程OracleTiDB
- SQL Server 操作要重建表被禁止SQLServer