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

posted on 2009-06-14 21:36  jinzhenshui  阅读(406)  评论(0编辑  收藏  举报