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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Sql server 2005遷移至Oracle系列之二：生成儲存過程SQLServerOracle儲存過程
- Sql server 2005遷移至Oracle系列之三：在Oracle中建立sql中的常見函式SQLServerOracle函式
- Sql server 2005遷移至Oracle系列之五：角色、使用者、及許可權SQLServerOracle
- Sql server 2005遷移至Oracle系列之四：在Oracle中建立位或運算函式-bitorSQLServerOracle函式
- MSSQL Server 遷移至 ORACLE解決方案SQLServerOracle
- SQL Server 遷移至MySQL 關鍵步驟的梳理總結ServerMySql
- 將表遷移至其他表空間
- SQL SERVER 2005表分割槽功能SQLServer
- Oracle資料遷移至MySQLOracleMySql
- SQL Server 2005分割槽表例項SQLServer
- SQL Server 2005系列教學_ 觸發器SQLServer觸發器
- 從Sql Server遷移資料到OracleSQLServerOracle
- Sql Server系列：資料表操作SQLServer
- Sql Server系列：分割槽表操作SQLServer
- .NET框架下Oracle到SQL Server遷移框架OracleSQLServer
- SQL server 2005 expressSQLServerExpress
- SQL Server 2000/2005資料字典生成方法SQLServer
- SQL Server 2005系列教學(14) 使用者管理SQLServer
- 移至64位SQL Server資料庫(轉)SQLServer資料庫
- Oracle 11g Windows 遷移至 LinuxOracleWindowsLinux
- SQL 2005的SSIS與Oracle的遷移效能(轉)SQLOracle
- oracle11g gateway for sql server2005配置OracleGatewaySQLServer
- SQL Server2005 表分割槽三步曲SQLServer
- 將應用程式從Sql Server遷移到OracleSQLServerOracle
- Sql Server 2005函式SQLServer函式
- SQL SERVER 2005 配置－saSQLServer
- SQL SERVER2005建Link ServerSQLServer
- oracle11g pivot 行列轉換 SQL Server 2005OracleSQLServer
- SQL Server 2005和Oracle高可用性對比SQLServerOracle
- 在SQL Server 2005中實現表的行列轉換SQLServer
- SQL Server 2005 中的分割槽表和索引應用SQLServer索引
- SQL Server 2005中各個系統表的作用(轉)SQLServer
- Sql Server 2005新增T-sql特性SQLServer
- [譯] 系列教程：如何將程式碼遷移至 TensorFlow 1.0
- XML Support in Microsoft SQL Server 2005XMLROSSQLServer
- PowerShell連線 SQL Server 2005SQLServer
- Monitoring Tempdb in SQL Server 2005SQLServer
- 微軟SQL Server 2005速成版微軟SQLServer