Sybase数据库技术，数据库恢复专家

 提供一个能够自动生成Sybase数据库内所有表的表结构、索引语法的存储过程。

存储过程名字：sp_gettabledll 下载

过程语法如下:

----------------------------------------------------------------------------------------------------------------------

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

create procedure sp_gettabledll 
@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_gettabledll  
                @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