分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
--经常在用,感觉还不错。在数据移植的时候,配上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)
给我老师的人工智能教程打call!http://blog.csdn.net/jiangjunshow