SYBASE中生成所有建表语句的过程

--经常在用，感觉还不错。在数据移植的时候，配上BCP，那可是非常的方便

if exists(select 1 from sysobjects where name = 'sp_gent' and type = 'P')
  drop procedure sp_gent
go

create procedure sp_gent  
@tblname varchar(30) = null,  
@prechar varchar(4) = null,   --$:no print  
@table_dll varchar(16384) = null out,  
@dbname varchar(32) = null,  
@droptg char(1) = '1',  
@prxytx varchar(255) = null,  
@replace varchar(20) = null,  
@tabtype varchar(1) = 'A', --A:所有表;P:代理表;U:用户表  
@indextg varchar(3) = 'TPI',  --T:纯表;P:主键;I:纯索引;J:除主键外的纯索引（和TP使用与I相同,和I同时使用I失效）  
@table_seg varchar(32) = null,  
@index_seg varchar(32) = null  
as  
begin  
     set nocount on   
  
    if @tblname is null begin  
        declare @c_tblname varchar(30)  
        declare cur_1 cursor for  
        select name from sysobjects where type = 'U' order by name  
        open cur_1  
        fetch cur_1 into @c_tblname  
        while @@sqlstatus = 0 begin  
            exec sp_gent   
                @tblname = @c_tblname,  
                @prechar = @prechar,  
                @dbname  = @dbname ,  
                @droptg  = @droptg ,  
                @prxytx  =  @prxytx ,  
                @replace = @replace,  
                @tabtype = @tabtype, --A:所有表;P:代理表;U:用户表  
                @indextg = @indextg, --A:表和索引;T:纯表;I:纯索引  
                @table_seg = @table_seg,  
                @index_seg = @index_seg  
            fetch cur_1 into @c_tblname  
        end  
        close cur_1  
        deallocate cursor cur_1  
        return  
    end  
  
    declare @obj_id int  
    declare @sysstat2 int  
    declare @username varchar(30)  
  
     select @obj_id = id, @sysstat2 = sysstat2 ,@username  = user_name(uid)  
        from sysobjects where name = @tblname and type = 'U'  
    if @@rowcount <> 1  
    begin  
        print 'table %1! not exists', @tblname  
        goto err  
    end  
     if @sysstat2 & 1024 = 1024 begin  
        if upper(@tabtype) in ('U')  
            goto ok  
    end  
    else begin  
        if upper(@tabtype) in ('P')  
            goto ok  
    end  
  
    declare @colname varchar(30)        --列名  
    declare @typename varchar(30)       --类型名称  
    declare @usertype smallint          --类型ID  
    declare @length int                 --长度  
    declare @prec tinyint               --有效位数  
    declare @scale tinyint              --精度  
    declare @def_id int             --默认值id  
    declare @nulls tinyint              --空值  
    declare @ident tinyint              --标识列  
    declare @index_dll varchar(16384)  
  
    declare @def_text varchar(100)  
    declare @ide_text varchar(30)  
    declare @nul_text varchar(30)  
  
    declare @cns_text varchar(500)  
    declare @uni_pri varchar(40), @non_clu varchar(40), @non_uni varchar(40)  
  
    declare @lock_scheme varchar(100)  
  
    declare @keys varchar(500), @i int  
    declare @thiskey varchar(30)  
    declare @sorder char(4)  
    select @keys = "", @i = 1  
  
    declare @cns_name varchar(30), @status int, @indid int  
    declare @idx_name varchar(50)  
  
    declare @CRNW varchar(2)    --回车换行  
    declare @TAB char(1)  
  
    select @CRNW = convert(varchar(2), 0x0d0a)  
    select @TAB = convert(char(1), 0x09)  
  
    declare @dbname_dot varchar(35)  
    if ltrim(@dbname) is null  
        select @dbname = null,@dbname_dot = null  
    else  
        select @dbname = @dbname + '.',@dbname_dot = @dbname + '..'  
  
    declare @table_name varchar(30)  
    select @table_name = case when ltrim(@replace) is not null then @replace else @tblname end  
  
    declare @prefix_table varchar(2)  
    select @prefix_table = case when ltrim(@prxytx) is not null then 'r_' else null end  
  
    if charindex('T',@indextg) > 0 begin  
        if @droptg <> '0'  
            select @table_dll = "if exists(select 1 from "+@dbname_dot  
            +"sysobjects where name = '"+@prefix_table  
            +@table_name+"' and type = 'U')"  
            +@CRNW+@TAB+'drop table '+@dbname+@username + '.'  
            +@prefix_table  
            +@table_name+@CRNW  
            +case when @sysstat2 & 1024 = 1024  
                     then @TAB+'exec sp_dropobjectdef '+@table_name+@CRNW  
                when ltrim(@prxytx) is not null  
                    then @TAB+'exec sp_dropobjectdef r_'+@table_name+@CRNW  
                else null   
            end  
            +'go'+@CRNW  
        else  
            select @table_dll = null  
      
        if @sysstat2 & 1024 = 1024 begin  
            declare @OS_file varchar(255)  
            select @OS_file = char_value from sysattributes  
                    where class = 9 and attribute = 1 and  
                    object_cinfo = @tblname  
            if @@rowcount = 0 begin  
                print '取代理表前缀失败%1!', @tblname  
                goto err  
            end  
            select @table_dll = @table_dll+"exec sp_addobjectdef "  
            +@table_name  
            +", '"+@OS_file+"', 'table'"+@CRNW+  
            "create existing table " + @dbname+@username + "."  
            +@table_name + " ("  
        end  
        else if ltrim(@prxytx) is not null  
             select @table_dll = @table_dll+"exec sp_addobjectdef r_"  
            +@table_name+", '"+@prxytx  
            +@table_name+"', 'table'"+@CRNW  
            +"create existing table " + @dbname+@username + ".r_"  
            +@table_name + " ("  
         else  
            select @table_dll = @table_dll+'create table ' + @dbname+@username + '.'  
            +@table_name + ' ('  
      
        --如果在sybsystemprocs数据库下提交，以下注释掉  
          
        declare @tablna varchar(255)  
         --select @tablna = tablna from knp_tabl where tablcd = @tblname  
        --if @@rowcount = 0  
            select @tablna = null  
        if ltrim(@tablna) is not null  
            select @table_dll = @table_dll + '    --'+@tablna  
      
        select @prechar = case when @prechar is not null then left(@prechar+space(4),4) else @prechar end  
        if @prechar <> '$' begin  
            if @prechar is not null begin  
                declare @temp_dll varchar(16384),@print_dll varchar(16384)  
                 select @temp_dll = @table_dll  
                select @temp_dll = @prechar + @temp_dll  
                while charindex(@CRNW,@temp_dll) > 0 and char_length(@temp_dll) <> charindex(@CRNW,@temp_dll)+1 begin  
                    select @print_dll = @print_dll + left(@temp_dll,charindex(@CRNW,@temp_dll) - 1) + @CRNW+@prechar  
                    select @temp_dll = substring(@temp_dll,charindex(@CRNW,@temp_dll)+char_length(@CRNW),char_length(@temp_dll))  
                end  
                 select @print_dll = @print_dll + @temp_dll  
                print '%1!',@print_dll  
            end  
            else  
                print '%1!',@table_dll  
        end  
          
        select @table_dll = @table_dll + @CRNW  
      
         if ltrim(@table_seg) is null begin  
            select @table_seg = s.name  
                from sysobjects o, syssegments s, sysindexes i  
                    where o.id = object_id(@tblname)  
                        and i.id = o.id  
                         and i.indid < 2  
                        and i.segment = s.segment  
            if @@rowcount = 0 begin   
                print '表%1!所在的段不存在',@tblname  
                goto err  
            end  
        end  
    end  
  
    --确定表是否有完整性约束  
    declare @have_con char(1)  
    if exists (select 1 from sysindexes where id = @obj_id and status2 & 2 = 2 )  
        and (ltrim(@prxytx) is null or @sysstat2 & 1024 = 1024)  
        select @have_con = '1'  
    else  
         select @have_con = '0'  
  
  
    if charindex('T',@indextg) > 0 begin  
        declare @col_int int  
        select @col_int = count(*) from syscolumns  
            where id = @obj_id  
      
        declare cur_col cursor for  
            select b.name, b.usertype, c.name , b.length, b.prec, b.scale, b.cdefault,  
                    convert(bit,b.status&8) as Nulls,  
                    convert(bit,b.status&128) as Ident  
                from sysobjects a, syscolumns b, systypes c  
                 where a.name = @tblname and a.type = 'U'  
                    and  a.id = b.id  
                    and b.usertype = c.usertype  
                order by b.colid  
      
        open cur_col  
        fetch cur_col into @colname, @usertype, @typename, @length, @prec, @scale, @def_id, @nulls, @ident  
        while @@sqlstatus = 0  
        begin  
            --系统定义的数据类型  
            if  @usertype < 100  
            begin  
      
                if rtrim(@typename) in ('char','varchar','nchar','nvarchar')  
                    select @typename = @typename + '('+ convert(varchar,@length) +')'  
                else if @typename in ('numeric','decimal')  
                    select @typename = @typename + '(' + convert(varchar,@prec) +  ',' + convert(varchar,@scale) + ')'  
                else if @typename in ('float','double')  
                    select @typename = @typename + '(' + convert(varchar,@prec) + ')'  
                else if @typename in ('binary','varbinary')  
                     select @typename =  @typename + '(' + convert(varchar,@length) + ')'  
            end  
      
            select @ide_text = case @ident when 1 then 'identity' else null end  
            select @nul_text = case @nulls when 1 then '    null'  else 'not null' end  
      
            if @def_id > 0  
            begin  
                select @def_text = ltrim(rtrim(b.text))  
                    from sysobjects a, syscomments b  
                        where a.id = @def_id and a.id = b.id  
                 if @@rowcount <> 1  
                begin  
                    print '取default失败%1!', @def_id  
                    goto err  
                end  
                while charindex(@TAB,@def_text) > 0  
                    select @def_text = stuff(@def_text,charindex(@TAB,@def_text),char_length(@TAB),' ')  
                while charindex('  ',@def_text) > 0  
                    select @def_text = stuff(@def_text,charindex('  ',@def_text),char_length('  '),' ')  
                select @def_text = rtrim(ltrim(@def_text))  
      
            end  
            else  
                select @def_text = null  
      
            declare @thiscol varchar(500)  
            select @thiscol =  
                    case when char_length(@colname) <= 10 then left(@colname+space(10),10) else @colname end   
                    + ' ' + case when char_length(@typename) <= 15 then left(@typename+space(15),15) else @typename end  
                    + ' ' + @def_text  
                    + ' ' + @ide_text  
                    + ' ' + @nul_text  
      
            if @i = @col_int and (@have_con = '0' or charindex('P',@indextg) <= 0)  
                select @thiscol  = @thiscol +  '  '  
            else  
                select @thiscol  = @thiscol + ' ,'  
      
            --如果在sybsystemprocs数据库下提交，以下注释掉  
            declare @colmna varchar(255)  
            select @colmna = null  
            --select @colmna = colmna from knp_colm where tablcd = @tblname and colmcd = @colname  
      
            if ltrim(@colmna) is not null  
                select @thiscol = @thiscol + '    --'+@colmna  
      
            if @prechar <> '$'  
                print '%1!%2!',@prechar, @thiscol  
      
            select @table_dll = @table_dll + @thiscol + @CRNW  
      
            select @i = @i + 1  
            fetch cur_col into @colname, @usertype, @typename, @length, @prec, @scale, @def_id, @nulls, @ident  
        end  
    end  
  
    if @have_con = '1' and charindex('P',@indextg) > 0  
    begin  
  
        select @cns_name = name, @status = status, @indid = indid  
            from sysindexes where id = @obj_id and status2 & 2 = 2  
  
        --print 'exist constraint... status = %1!', @status  
  
        if @indid = 1   
            select @non_clu = 'clustered'  
        else if @indid > 1  
        begin  
            if  @status & 16 = 16  
                select @non_clu = 'clustered'  
            else  
                select @non_clu = 'nonclustered'  
        end  
  
        if @status & 2048 = 2048  
            select @uni_pri = 'primary key'  
        else  
            select @uni_pri = 'unique'  
  
        select @cns_text = 'constraint ' + @cns_name + ' ' + @uni_pri + ' ' + @non_clu  
  
        select   @i = 1, @keys = ''  
        select @thiskey = index_col(@tblname, @indid, @i)  
        while @thiskey <> null  
        begin  
            if @i > 1  
            begin  
                select @keys = @keys + ", "  
            end  
  
            if ltrim(@keys) is null  
                select @keys = @thiskey  
            else  
                select @keys = @keys + @thiskey  
  
            select @sorder = index_colorder(@tblname, @indid, @i)  
            if (@sorder = "DESC")  
                 select @keys = @keys + " " + @sorder  
  
            select @i = @i + 1  
            select @thiskey = index_col(@tblname, @indid, @i)  
        end  
  
        select @cns_text = @cns_text + ' (' + @keys + ')'  
  
        if ltrim(@table_seg) is null begin  
            select @table_seg = s.name  
                from sysobjects o, syssegments s, sysindexes i  
                    where o.id = object_id(@tblname)  
                        and i.id = o.id  
                        and i.indid < 2   
                        and i.segment = s.segment  
            if @@rowcount = 0 begin  
                print '表%1!所在的段不存在',@tblname  
                goto err  
            end  
        end  
  
        if charindex('T',@indextg) <= 0  
             select @cns_text = 'alter table '+@dbname+@username + '.'+@table_name+' add '+@cns_text+ " on '" + @table_seg + "'"  
        if @prechar <> '$'  
            print '%1!%2!',@prechar,@cns_text  
  
        select @table_dll = @table_dll + @cns_text  
  
    end  
  
    if charindex('T',@indextg) > 0 begin  
        if @prechar <> '$'  
            print '%1!%2!',@prechar, ') '  
      
        select @table_dll = left(@table_dll,char_length(@table_dll)-1) +  @CRNW + ')'  
      
        --表锁计划  
        if @sysstat2 & 8192 = 8192  
            select @lock_scheme = 'lock allpages'  
        else if @sysstat2 & 16384 =  16384  
            select @lock_scheme = 'lock datapages'  
        else if @sysstat2 & 32768 = 32768  
            select @lock_scheme = 'lock datarows'  
        select @table_dll = @table_dll + @CRNW + @lock_scheme  
      
        if @prechar <> '$'  
            print '%1!%2!',@prechar, @lock_scheme  
      
        select @table_seg = "on '"+ @table_seg+"'"  
        select @table_dll = @table_dll + @CRNW + @table_seg + @CRNW+'go'+@CRNW  
      
        if @prechar <> '$' begin  
            print '%1!%2!',@prechar, @table_seg  
            print '%1!go',@prechar  
        end  
    end  
  
    if ltrim(@prxytx)  is not null or @sysstat2 & 1024 = 1024  
        goto ok  
  
    if charindex('T',@indextg) > 0 begin  
        declare @part_num int,@partition varchar(255)  
        select @part_num = count(*)  
            from syspartitions  
            where id = object_id(@tblname)  
        if @part_num <> 0 begin  
            select @partition = 'alter table '+ @username + '.' + @table_name + ' partition '+convert(varchar,@part_num)  
            select @table_dll = @table_dll + @CRNW + @partition  
            if @prechar <> '$'  
                print '%1!%2!',@prechar, @partition  
        end  
    end  
  
    --select @table_dll as table_dll  
    -------------------------------------------------------------------------------------  
    --检查其他索引  
    declare @idx_seg  varchar(32)  
    if charindex('I',@indextg) > 0 or charindex('J',@indextg) > 0 begin  
        if exists  (select 1 from sysindexes where id = @obj_id and indid <> 0 and   
            (status2 & 2 <> 2 or charindex('P',@indextg) <= 0 and charindex('J',@indextg) <= 0))  
  
        begin  
            declare cur_idx cursor for  
                select name, indid, status from sysindexes  
                    where id = @obj_id and indid <> 0 and   
            (status2 & 2 <> 2 or charindex('P',@indextg) <= 0 and charindex('J',@indextg) <= 0)  
--                  (status2 & 2 <> 2 or charindex('P',@indextg) <= 0)  
            open cur_idx  
            fetch cur_idx into @idx_name, @indid, @status  
            while @@sqlstatus = 0   
            begin  
      
                if @indid = 1  
                    select @non_clu = 'clustered'  
                else if @indid > 1  
                begin  
                    if  @status & 16 = 16  
                        select @non_clu = 'clustered'  
                    else  
                        select @non_clu = 'nonclustered'  
                end  
      
                if @status & 2 = 2  
                    select @non_uni = 'unique '  
                else  
                     select @non_uni = null  
      
                select @i = 1,@keys = ''  
                select @thiskey = index_col(@tblname, @indid, @i)  
                while @thiskey <> null  
                begin  
                    if @i > 1  
                      begin  
                        select @keys = @keys + ", "  
                    end  
      
                    if ltrim(@keys) is null  
                        select @keys = @thiskey  
                    else  
                         select @keys = @keys + @thiskey  
      
                    select @sorder = index_colorder(@tblname, @indid, @i)  
                    if @sorder = "DESC"  
                        select @keys = @keys + " " + @sorder  
      
                    select @i = @i + 1  
                    select @thiskey = index_col(@tblname, @indid, @i)  
                end  
      
                if ltrim(@index_seg) is null begin   
                    select @idx_seg = s.name  
                        from syssegments s, sysindexes i  
                            where s.segment = i.segment  
                                and i.id = object_id(@tblname)  
                                and i.indid = @indid  
                    if @@rowcount = 0 begin  
                         print '索引%1!所在的段不存在',@idx_name   
                        goto err  
                    end  
                end  
                else  
                    select @idx_seg = @index_seg  
      
                if ltrim(@keys) is not null begin  
                    declare @thisidx varchar(500)  
                    select @thisidx = 'create ' + @non_uni  
                        + @non_clu + ' index ' + @idx_name + ' on ' + @dbname + @username  
                        + '.' + @table_name + "(" + @keys + ") on '" +@idx_seg+"'"  
      
                    select @index_dll = @index_dll + @thisidx + @CRNW  
                    if @prechar <> '$'  
                        print '%1!%2!',@prechar, @thisidx  
                end   
      
                fetch cur_idx into @idx_name, @indid, @status  
            end  
      
            if ltrim(@index_dll) is not null begin  
                if @droptg <> '0' begin  
                    select @index_dll = @index_dll + 'go' + @CRNW  
                    if @prechar <> '$'  
                        print '%1!go',@prechar  
                end  
            end  
      
            select @table_dll = @table_dll + @CRNW + @index_dll  
        end  
    end  
ok:  
    set nocount off  
    return 0  
err:  
    set nocount off  
    return -1  
end  
go