SQL SERVER 2008 复制所有表结构、触发器、存储过程、视图等(海典传输初始化)(一)
USE [……] GO /****** Object: StoredProcedure [dbo].[p_init_busno] Script Date: 08/18/2022 16:14:53 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /*--------------------------------------------------------------- 功能:得到创建数据库代码,和初始化新业务机构的语句 gthlu 2007-06-04 ---------------------------------------------------------------*/ CREATE proc [dbo].[p_init_busno](@busno varchar(10),@userid varchar(10)) as declare @sql varchar(1000) declare @maxorderid int,@orderid int,@zoneno varchar(10) declare @procname nvarchar(100) --使有替换存储过程设置查询是否有替换存储过程 YB 2020-5-13 set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID)) If @procname > '' Begin set @procname = 'exec ' + @procname + ' @busno,@userid' execute sp_executesql @procname, N'@busno varchar(10),@userid varchar(10)', @busno,@userid return End if not exists(select * from c_org_busi where busno = @busno ) begin raiserror("指定的业务机构编码没有在“业务机构代码”里设置好!",16,1) return end if not exists(select * from c_user_org where org_tran_code = @busno and userid = @userid and status = 1) begin raiserror("请先将您自己的权限在“用户对应机构”中设置为能使用目标业务机构!",16,1) return end if not exists(select * from c_org_busi b,c_zone c where b.busno=c.busno and b.busno=@busno) begin raiserror("指定的业务机构编码没有在“库区设置”里设置好库区!",16,1) return end if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='11') begin raiserror("指定的业务机构编码没有在“货位设置”里设置好【正常货位】!",16,1) return end -- 2966 如果启用门店验收确认功能,还需要检查这两个货位 if exists(select * from c_sys_ini where ini='2966' and para='1') begin if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='21') begin raiserror("指定的业务机构编码没有在“货位设置”里设置好【待处理货位】!",16,1) return end if not exists(select * from c_org_busi b,c_zone c,c_stall d where b.busno=c.busno and c.zoneno=d.zoneno and b.busno=@busno and d.stalltype='22') begin raiserror("指定的业务机构编码没有在“货位设置”里设置好【不合格货位】!",16,1) return end end create table #data(sql varchar(8000),orderid int identity(1,1),orderid_temp int null) --免调试SQL系统参数 by liwenlong 20140521 insert into #data ( sql ) select 'SET ANSI_NULLS ON' insert into #data ( sql ) select 'go' insert into #data ( sql ) select 'SET QUOTED_IDENTIFIER OFF' insert into #data ( sql ) select 'go' --免调SQL试系统参数 结束 by liwenlong 20140521 insert into #data(sql) exec p_get_usertable insert into #data(sql) select 'go' insert into #data(sql) exec p_get_usertable_primary insert into #data(sql) select 'go' insert into #data(sql) exec p_get_usertable_index insert into #data(sql) select 'go' insert into #data(sql) exec p_get_objtext insert into #data(sql) select 'go' select @orderid = max(orderid) from #data insert into #data(sql) select "ALTER TABLE c_sys_ini disable trigger all" insert into #data(sql) exec p_get_select 'c_sys_ini','ini<>''1006'' and ini<>''1007'' and ini<>''1008'' and ini<>''1009''' insert into #data(sql) select "ALTER TABLE c_sys_ini enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "update c_sys_ini set para = '" + @busno + "' where ini = '1001'" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_dddw disable trigger all" insert into #data(sql) exec p_get_select 'c_dddw','' insert into #data(sql) select "ALTER TABLE c_dddw enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_bill disable trigger all" insert into #data(sql) exec p_get_select 'c_bill ','' insert into #data(sql) select "ALTER TABLE c_bill enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_columns disable trigger all" insert into #data(sql) exec p_get_select 'c_columns ','' insert into #data(sql) select "ALTER TABLE c_columns enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_dyreport disable trigger all" insert into #data(sql) exec p_get_select 'c_dyreport ','' insert into #data(sql) select "ALTER TABLE c_dyreport enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_function disable trigger all" insert into #data(sql) exec p_get_select 'c_function','' insert into #data(sql) select "ALTER TABLE c_function enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_function_ext disable trigger all" insert into #data(sql) exec p_get_select 'c_function_ext','' insert into #data(sql) select "ALTER TABLE c_function_ext enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_sale_printer disable trigger all" insert into #data(sql) exec p_get_select 'c_sale_printer','' insert into #data(sql) select "ALTER TABLE c_sale_printer enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_help disable trigger all" insert into #data(sql) exec p_get_select 'c_help','' insert into #data(sql) select "ALTER TABLE c_help enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_objects disable trigger all" insert into #data(sql) exec p_get_select 'c_objects','' insert into #data(sql) select "ALTER TABLE c_objects enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_period disable trigger all" insert into #data(sql) exec p_get_select 'c_period','' insert into #data(sql) select "ALTER TABLE c_period enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_print_m disable trigger all" insert into #data(sql) exec p_get_select 'c_print_m','' insert into #data(sql) select "ALTER TABLE c_print_m enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_print_c disable trigger all" insert into #data(sql) exec p_get_select 'c_print_c','' insert into #data(sql) select "ALTER TABLE c_print_c enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_proc_set disable trigger all" insert into #data(sql) exec p_get_select 'c_proc_set','' insert into #data(sql) select "ALTER TABLE c_proc_set enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_relation disable trigger all" insert into #data(sql) exec p_get_select 'c_relation','' insert into #data(sql) select "ALTER TABLE c_relation enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_retrieve disable trigger all" insert into #data(sql) exec p_get_select 'c_retrieve','' insert into #data(sql) select "ALTER TABLE c_retrieve enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE t_tranitem disable trigger all" insert into #data(sql) exec p_get_select 't_tranitem','' insert into #data(sql) select "ALTER TABLE t_tranitem enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_stroke disable trigger all" insert into #data(sql) exec p_get_select 'c_stroke','' insert into #data(sql) select "ALTER TABLE c_stroke enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_py disable trigger all" insert into #data(sql) exec p_get_select 'c_py','' insert into #data(sql) select "ALTER TABLE c_py enable trigger all" insert into #data(sql) select 'go' select @sql = "insert into #data(sql) exec p_get_select 'c_org_tran'," + """where org_tran_code = '" + @busno + "'""" exec( @sql) select @sql = "insert into #data(sql) exec p_get_select 'c_org_tran'," + """where org_tran_code = '" + rtrim(dbo.f_sys_ini('1001')) + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_org_busi disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_org_busi'," + """where busno = '" + @busno + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_org_busi enable trigger all" insert into #data(sql) select "ALTER TABLE c_user disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user enable trigger all" insert into #data(sql) select "ALTER TABLE c_user_func disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user_func'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user_func enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_user_func_ext disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user_func_ext'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user_func_ext enable trigger all" insert into #data(sql) select "ALTER TABLE c_user_func_shop disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user_func_shop'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user_func_shop enable trigger all" insert into #data(sql) select 'go' insert into #data(sql) select "ALTER TABLE c_user_org disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user_org'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user_org enable trigger all" insert into #data(sql) select "ALTER TABLE c_user_dept disable trigger all" select @sql = "insert into #data(sql) exec p_get_select 'c_user_dept'," + """where userid = '" + @userid + "'""" exec( @sql) insert into #data(sql) select "ALTER TABLE c_user_dept enable trigger all" update #data set orderid_temp = orderid select @maxorderid = max(orderid) from #data while @orderid < @maxorderid begin select @orderid = @orderid + 10 insert into #data(sql,orderid_temp) select 'go',@orderid end insert into #data(sql) select 'go' --新店所有的老邮件附件不传下去 update t_transet_c set maxserialno = convert(bigint,@@dbts) where tranid = '004' and tranorg_obj = @busno select sql from #data order by orderid_temp drop table #data GO
其中,存储过程 p_get_usertable 代码如下:
/*---------------------------------------------------------------------- 功能:取得一个数据库里所有用户表的建表代码 by gthlu 2005-10-15 -----------------------------------------------------------------------*/ CREATE proc [dbo].[p_get_usertable] as declare @sql varchar(4000) declare @tabname varchar(100) declare @colname varchar(80),@cdefault int,@domain int,@xusertype smallint, @prec smallint,@scale int,@isnullable int,@autoval int ,@ctext_compute varchar(8000) declare @procname nvarchar(100) declare @typeName sysname,@xscale tinyint --使有替换存储过程设置查询是否有替换存储过程 YB 2020-5-13 set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID)) If @procname > '' Begin set @procname = 'exec ' + @procname execute sp_executesql @procname return End create table #tabtext(txt varchar(4000),orderid int identity(1,1)) declare tabname_cursor cursor for select a.name from sysobjects a,sysusers b where a.xtype = 'u' and b.uid = a.uid and b.name = 'dbo' --and a.name = 't_ol_order_m' open tabname_cursor fetch next from tabname_cursor into @tabname while @@fetch_status >= 0 begin select @sql = 'if object_id(''' + @tabname + ''') is null ' + 'create table [' + @tabname + '](' --修正sql2005下 autoval 不能正确判断自增长字段--by黄才旺 2008-12-17 declare column_cursor cursor for select syscolumns.name,syscolumns.cdefault,syscolumns.domain,syscolumns.xusertype,syscolumns.prec,c.name as typeName, syscolumns.scale,syscolumns.isnullable ,COLUMNPROPERTY(syscolumns.id,syscolumns.name,'IsIdentity') ,b.text,c.xscale from syscolumns left join syscomments b on syscolumns.ID=b.ID AND syscolumns.Colid=b.number join systypes c on syscolumns.xusertype = c.xusertype where syscolumns.id = object_id(@tabname) order by syscolumns.colid open column_cursor fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@typeName, @scale,@isnullable ,@autoval ,@ctext_compute,@xscale while @@fetch_status >= 0 begin if len(@sql)>3600 begin insert into #tabtext(txt) select @sql set @sql='' end if len(isnull(@ctext_compute,''))>0 select @sql=@sql + '[' + @colname + '] AS ' + @ctext_compute + ',' else begin select @sql = @sql + '[' + @colname + ']' + ' ' + @typeName + case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype and (status <> 2 and name not in ('binary','char','nchar','nvarchar','varbinary','varchar') )) then ' ' else '(' + cast(case when @prec = -1 then case when @typeName in ('nchar','nvarchar') then 4000 else 8000 end else @prec end as varchar(10)) + case when @scale is null or @scale = @xscale then ')' else ',' + cast(@scale as varchar(10)) + ')' end end + case when @cdefault is null or @cdefault = 0 then '' else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end + case @isnullable when 0 then ' not null' when 1 then ' null' else ' ' end + case when @autoval <> 1 then ',' else ' identity(' + convert(varchar,ident_seed(@tabname)) + ',' + convert(varchar,ident_incr(@tabname)) + '),' end --select @sql = @sql + '[' + @colname + ']' + (select ' ' + name from systypes where xusertype = @xusertype) -- + case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype and -- (status <> 2 and name not in ('binary','char','nchar','nvarchar','varbinary','varchar') )) then ' ' -- else '(' + cast(case when @prec = -1 then 8000 else @prec end as varchar(10)) -- + case when @scale is null or @scale = (select scale from systypes where xusertype = @xusertype) then ')' -- else ',' + cast(@scale as varchar(10)) + ')' end end -- + case when @cdefault is null or @cdefault = 0 then '' -- else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end -- + case @isnullable when 0 then ' not null' -- when 1 then ' null' -- else ' ' end -- + case when @autoval <> 1 then ',' -- else ' identity(' + convert(varchar,ident_seed(@tabname)) + ',' + convert(varchar,ident_incr(@tabname)) + '),' end end fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@typeName, @scale,@isnullable,@autoval ,@ctext_compute,@xscale end close column_cursor deallocate column_cursor fetch next from tabname_cursor into @tabname select @sql = left(@sql,len(rtrim(@sql))-1) + ')' insert into #tabtext(txt) select @sql insert into #tabtext(txt) select 'go' end close tabname_cursor deallocate tabname_cursor --------------绑定列 insert into #tabtext(txt) select txt = 'if object_id(''[' + name + ']'') is null exec(''' + replace(text,'''','''''') + ''')' from sysobjects a,syscomments b where a.id = b.id and a.xtype = 'D' and b.text like '%create%' insert into #tabtext(txt) select 'go' insert into #tabtext(txt) select 'exec sp_bindefault ''[' + a.name + ']'',''[' + object_name(c.id) + '].[' + c.name + ']''' from sysobjects a,syscomments b,syscolumns c where a.id = b.id and a.xtype = 'D' and b.text like '%create%' and a.id = c.cdefault select txt from #tabtext order by orderid drop table #tabtext GO
存储过程 p_get_usertable_primary代码如下:
/*---------------------------------------------------------------------- 功能:得到当前数据库里所有表的主键代码 by gthlu 2007-06-02 -----------------------------------------------------------------------*/ CREATE proc [dbo].[p_get_usertable_primary] as DECLARE @table_id int DECLARE @table_name nvarchar(255) declare @colname varchar(255) declare @pkname varchar(255) declare @sql varchar(4000) declare @procname nvarchar(100) --使有替换存储过程设置查询是否有替换存储过程 YB 2020-5-13 set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID)) If @procname > '' Begin set @procname = 'exec ' + @procname execute sp_executesql @procname return End --select @table_name = 'u_sale_c' create table #pktext(txt varchar(4000),orderid int identity(1,1)) declare tabname_cursor cursor for select a.name from sysobjects a,sysindexes b,sysusers c where a.type = 'u' and a.id = b.id and (b.status & 0x800) = 0x800 and a.uid = c.uid and c.name = 'dbo' open tabname_cursor fetch next from tabname_cursor into @table_name while @@fetch_status >= 0 begin SELECT @table_id = object_id(@table_name) select colname = convert(sysname,c.name),o.name,pkname = convert(sysname,i.name), rowid = case when c.name = index_col(@table_name, i.indid, 1) then convert (smallint,1) when c.name = index_col(@table_name, i.indid, 2) then convert (smallint,2) when c.name = index_col(@table_name, i.indid, 3) then convert (smallint,3) when c.name = index_col(@table_name, i.indid, 4) then convert (smallint,4) when c.name = index_col(@table_name, i.indid, 5) then convert (smallint,5) when c.name = index_col(@table_name, i.indid, 6) then convert (smallint,6) when c.name = index_col(@table_name, i.indid, 7) then convert (smallint,7) when c.name = index_col(@table_name, i.indid, 8) then convert (smallint,8) when c.name = index_col(@table_name, i.indid, 9) then convert (smallint,9) when c.name = index_col(@table_name, i.indid, 10) then convert (smallint,10) when c.name = index_col(@table_name, i.indid, 11) then convert (smallint,11) when c.name = index_col(@table_name, i.indid, 12) then convert (smallint,12) when c.name = index_col(@table_name, i.indid, 13) then convert (smallint,13) when c.name = index_col(@table_name, i.indid, 14) then convert (smallint,14) when c.name = index_col(@table_name, i.indid, 15) then convert (smallint,15) when c.name = index_col(@table_name, i.indid, 16) then convert (smallint,16) end into #t from sysindexes i, syscolumns c, sysobjects o where o.id = @table_id and o.id = c.id and o.id = i.id and (i.status & 0x800) = 0x800 and (c.name = index_col (@table_name, i.indid, 1) or c.name = index_col (@table_name, i.indid, 2) or c.name = index_col (@table_name, i.indid, 3) or c.name = index_col (@table_name, i.indid, 4) or c.name = index_col (@table_name, i.indid, 5) or c.name = index_col (@table_name, i.indid, 6) or c.name = index_col (@table_name, i.indid, 7) or c.name = index_col (@table_name, i.indid, 8) or c.name = index_col (@table_name, i.indid, 9) or c.name = index_col (@table_name, i.indid, 10) or c.name = index_col (@table_name, i.indid, 11) or c.name = index_col (@table_name, i.indid, 12) or c.name = index_col (@table_name, i.indid, 13) or c.name = index_col (@table_name, i.indid, 14) or c.name = index_col (@table_name, i.indid, 15) or c.name = index_col (@table_name, i.indid, 16) ) select top 1 @pkname = pkname from #t declare primary_cursor cursor for select colname from #t order by rowid open primary_cursor fetch next from primary_cursor into @colname select @sql = "if not exists(select * from sysobjects where object_name(parent_obj) = '" + @table_name + "' and type = 'k') " + ' alter table ' + @table_name + ' add primary key (' while @@fetch_status >= 0 begin select @sql = @sql + @colname + ',' fetch next from primary_cursor into @colname end select @sql = left(@sql,len(rtrim(@sql))-1) + ')' insert into #pktext(txt) select @sql insert into #pktext(txt) select 'go' close primary_cursor deallocate primary_cursor drop table #t fetch next from tabname_cursor into @table_name end close tabname_cursor deallocate tabname_cursor select txt from #pktext order by orderid drop table #pktext GO
存储过程 p_get_usertable_index代码如下:
/*---------------------------------------------------------------------- 功能:得到当前数据库里所有索引创建代码 by gthlu 2007-06-02 -----------------------------------------------------------------------*/ CREATE proc [dbo].[p_get_usertable_index] as DECLARE @table_id int DECLARE @table_name nvarchar(255) declare @index_name varchar(255) declare @colname varchar(255) declare @sql varchar(4000) declare @status int declare @procname nvarchar(100) --使有替换存储过程设置查询是否有替换存储过程 YB 2020-5-13 set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID)) If @procname > '' Begin set @procname = 'exec ' + @procname execute sp_executesql @procname return End create table #pktext(txt varchar(4000)) declare tabname_cursor cursor for select a.name as table_name,b.name,b.status as index_name from sysobjects a,sysindexes b ,sysusers c where a.xtype = 'u' and a.id = b.id and (b.status & 0x800) <> 0x800 and (b.status & 64)=0 and indid > 0 and indid < 255 and c.name = 'dbo' and a.uid = c.uid open tabname_cursor fetch next from tabname_cursor into @table_name,@index_name,@status while @@fetch_status >= 0 begin SELECT @table_id = object_id(@table_name) select colname = convert(sysname,c.name),o.name,@index_name as index_name,@status as status, rowid = case when c.name = index_col(@table_name, i.indid, 1) then convert (smallint,1) when c.name = index_col(@table_name, i.indid, 2) then convert (smallint,2) when c.name = index_col(@table_name, i.indid, 3) then convert (smallint,3) when c.name = index_col(@table_name, i.indid, 4) then convert (smallint,4) when c.name = index_col(@table_name, i.indid, 5) then convert (smallint,5) when c.name = index_col(@table_name, i.indid, 6) then convert (smallint,6) when c.name = index_col(@table_name, i.indid, 7) then convert (smallint,7) when c.name = index_col(@table_name, i.indid, 8) then convert (smallint,8) when c.name = index_col(@table_name, i.indid, 9) then convert (smallint,9) when c.name = index_col(@table_name, i.indid, 10) then convert (smallint,10) when c.name = index_col(@table_name, i.indid, 11) then convert (smallint,11) when c.name = index_col(@table_name, i.indid, 12) then convert (smallint,12) when c.name = index_col(@table_name, i.indid, 13) then convert (smallint,13) when c.name = index_col(@table_name, i.indid, 14) then convert (smallint,14) when c.name = index_col(@table_name, i.indid, 15) then convert (smallint,15) when c.name = index_col(@table_name, i.indid, 16) then convert (smallint,16) end into #t from sysindexes i, syscolumns c, sysobjects o where o.id = @table_id and o.id = c.id and o.id = i.id and (i.status & 0x800) <> 0x800 and i.name = @index_name and indid > 0 and indid < 255 and (i.status & 64)=0 and (c.name = index_col (@table_name, i.indid, 1) or c.name = index_col (@table_name, i.indid, 2) or c.name = index_col (@table_name, i.indid, 3) or c.name = index_col (@table_name, i.indid, 4) or c.name = index_col (@table_name, i.indid, 5) or c.name = index_col (@table_name, i.indid, 6) or c.name = index_col (@table_name, i.indid, 7) or c.name = index_col (@table_name, i.indid, 8) or c.name = index_col (@table_name, i.indid, 9) or c.name = index_col (@table_name, i.indid, 10) or c.name = index_col (@table_name, i.indid, 11) or c.name = index_col (@table_name, i.indid, 12) or c.name = index_col (@table_name, i.indid, 13) or c.name = index_col (@table_name, i.indid, 14) or c.name = index_col (@table_name, i.indid, 15) or c.name = index_col (@table_name, i.indid, 16) ) select top 1 @index_name = index_name from #t declare primary_cursor cursor for select colname from #t order by rowid open primary_cursor fetch next from primary_cursor into @colname select @sql = "if exists (select * from sysindexes where name = '" + @index_name + "' and id = object_id('" + @table_name + "')) " + "drop index " + @table_name + "." + @index_name + ' CREATE '+ case WHEN (@status & 2)<>0 then 'UNIQUE ' ELSE '' END + 'INDEX ' + @index_name + ' ON ' + @table_name + '(' while @@fetch_status >= 0 begin select @sql = @sql + @colname + ',' fetch next from primary_cursor into @colname end select @sql = left(@sql,len(rtrim(@sql))-1) + ')' insert into #pktext select @sql insert into #pktext select 'go' close primary_cursor deallocate primary_cursor drop table #t fetch next from tabname_cursor into @table_name,@index_name,@status end close tabname_cursor deallocate tabname_cursor select * from #pktext drop table #pktext GO
存储过程 p_get_objtext代码如下:
/*-------------------------------------------------------------------------------- 功能:取数据库里的全部存储过程、触发器和视图,只适用于未加密的过程、触发器和视图 by gthlu 2005-10-14 修改:增加了取函数的代码,解决了因代码太长,导致截行问题,和视图嵌套调用后运行报对象无效错误。 2007-06-13 ---------------------------------------------------------------------------------*/ ALTER procedure [dbo].[p_get_objtext] as set nocount on declare @BlankSpaceAdded int ,@BasePos int ,@CurrentPos int ,@TextLength int ,@LineId int ,@AddOnLen int ,@LFCR int ,@DefinedLength int ,@SyscomText nvarchar(4000) ,@Line nvarchar(2000) ,@objid int ,@objname nvarchar(776) ,@objrowid int ,@maxorderid int ,@times int declare @procname nvarchar(100) --使有替换存储过程设置查询是否有替换存储过程 YB 2020-5-14 set @procname = dbo.f_get_rep_proc(OBJECT_NAME(@@PROCID)) If @procname > '' Begin set @procname = 'exec ' + @procname execute sp_executesql @procname return End Select @DefinedLength = 2000 SELECT @BlankSpaceAdded = 0 if OBJECT_ID('tempdb..#objtext') is not null drop table #objtext create table #objtext(txt nvarchar(2000),id int,LineId int) if OBJECT_ID('tempdb..#objname') is not null drop table #objname select distinct rtrim(object_name(a.id)) objname,case a.xtype when 'V' then 400 when 'FN' then 600 when 'IF' then 300 when 'TF' then 500 when 'TR' then 100 when 'P' then 200 else 0 end orderid ,a.xtype,a.id into #objname from sysobjects a (nolock),sysusers b (nolock),syscomments c (nolock) where a.xtype in ('FN','IF','TF','v','tr','p') and a.status >= 0 and a.uid = b.uid and b.name = 'dbo' and c.encrypted<>1 and a.id = c.id and left(lower(a.name),5) not in (lower('p_b2c'),lower('p_b2b')) and a.name not like 'jc_p_%' and not exists(select 1 from c_initsql_except where a.name = c_initsql_except.objectname) --p_b2c,p_b2b去掉电商的存储过程,因为里面用了些sql2000不支持的sql -----------------------------------------------视图被嵌套调用的先排序排在前面。 if OBJECT_ID('tempdb..#temp') is not null drop table #temp create table #temp (objname varchar(200),id int,row int identity(1,1)) declare @id int if OBJECT_ID('tempdb..#tmpmemts') is not null drop table #tmpmemts select d.id,d.text into #tmpmemts from #objname c ,syscomments d (nolock) where c.id = d.id and c.xtype = 'V' declare cursor_obj_v cursor for select rtrim(a.objname),a.id from #objname a where a.xtype = 'v' order by orderid open cursor_obj_v fetch next from cursor_obj_v into @objname,@id while @@fetch_status >= 0 begin if exists (select * from #tmpmemts where id <> @id and charindex(@objname ,text)>0) begin insert #temp (objname,id )select @objname,@id end fetch next from cursor_obj_v into @objname,@id end close cursor_obj_v deallocate cursor_obj_v --select * from #temp while 1 = 1 begin --select * into #order from #temp select @times = isnull(@times,0) + 1 --为了防止死循环,当循环次数大于10次后,跳出。 if @times > 10 break SELECT @maxorderid = max(orderid) FROM #objname where xtype = 'v' update a set orderid = orderid + row + abs(orderid - @maxorderid) from #objname a,#temp b where a.objname = b.objname if object_id('tempdb..#temp1') is not null drop table #temp1 select a.objname,identity(int,1,1) row into #temp1 from #objname a ,#tmpmemts b ,#temp e where a.id = b.id and a.xtype = 'v' and a.objname = e.objname and exists(select * from #objname c ,#tmpmemts d ,#temp f where c.id = d.id and c.xtype = 'V' and b.id <> d.id and c.objname = f.objname and charindex(a.objname ,text)>0) if @@rowcount = 0 and @times > 10 break truncate table #temp set identity_insert #temp on insert into #temp(objname,row) select objname,row from #temp1 drop table #temp1 end if object_id('tempdb..#tmpmemts') is not null drop table #tmpmemts if object_id('tempdb..#temp1') is not null drop table #temp1 --------------------------------------------------------------------- if OBJECT_ID('tempdb..#tmpmemts_all') is not null drop table #tmpmemts_all select d.id,d.text,number,colid into #tmpmemts_all from #objname c ,syscomments d (nolock) where c.id = d.id declare cursor_obj cursor for select objname,id from #objname order by orderid open cursor_obj fetch next from cursor_obj into @objname,@id select @objid = 1 while @@fetch_status >= 0 begin select @objrowid = isnull(@objrowid,0) + 1 if OBJECT_ID('tempdb..#CommentText') is not null drop table #CommentText CREATE TABLE #CommentText (LineId int, Text nvarchar(2000) collate database_default) insert into #commenttext select 0,'if object_id(''' + @objname + ''') is not null ' + case (select xtype from sysobjects (nolock) where id = object_id(@objname)) when 'v' then 'drop view ' + @objname when 'FN' then 'drop function ' + @objname when 'IF' then 'drop function ' + @objname when 'TF' then 'drop function ' + @objname when 'tr' then 'drop trigger ' + @objname when 'p' then 'drop proc ' + @objname else '' end insert into #commenttext select 1,'go' union all select 2,char(13) DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT text FROM #tmpmemts_all WHERE id = @id order by number,colid FOR READ ONLY SELECT @LFCR = 2 SELECT @LineId = 3 select @Line = '' OPEN ms_crs_syscom FETCH NEXT FROM ms_crs_syscom into @SyscomText WHILE @@fetch_status >= 0 BEGIN SELECT @BasePos = 1 SELECT @CurrentPos = 1 SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0 BEGIN SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) IF @CurrentPos != 0 BEGIN While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'') SELECT @BasePos = @CurrentPos+2 INSERT #CommentText VALUES( @LineId, @Line ) SELECT @LineId = @LineId + 1 SELECT @Line = NULL END ELSE BEGIN IF @BasePos <= @TextLength BEGIN While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'') if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0 BEGIN SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1 END END END END FETCH NEXT FROM ms_crs_syscom into @SyscomText END IF @Line is NOT NULL INSERT #CommentText VALUES( @LineId, @Line ) CLOSE ms_crs_syscom DEALLOCATE ms_crs_syscom insert into #commenttext select @lineid+1,char(13) union all select @lineid + 2,'go' union all select @lineid + 3,char(13) insert into #objtext select Text,@objrowid,lineid from #CommentText order by LineId select @objid = @objid + 1 DROP TABLE #CommentText fetch next from cursor_obj into @objname,@id end close cursor_obj deallocate cursor_obj select replace(txt,char(9),' ') from #objtext order by id desc,lineid drop table #objtext drop table #objname,#temp if OBJECT_ID('tempdb..#tmpmemts_all') is not null drop table #tmpmemts_all return (0)