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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
