清华大佬耗费三个月吐血整理的几百G的资源,免费分享!....>>>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | USE master go IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = Object_id(N '[dbo].[sp_copyProce]' ) AND Objectproperty(id, N 'IsProcedure' ) = 1) DROP PROCEDURE [dbo].[sp_copyProce] go CREATE PROC Sp_copyproce @s_dbname SYSNAME, --要复制存储过程的源数据库名 @d_dbname SYSNAME --目标数据库名 AS SET nocount ON IF Db_id(@s_dbname) IS NULL BEGIN RAISERROR( '数据库"%s"不存在' ,1,16,@s_dbname) RETURN END IF Db_id(@d_dbname) IS NULL BEGIN RAISERROR( '数据库"%s"不存在' ,1,16,@d_dbname) RETURN END SELECT @s_dbname = '[' + Replace (@s_dbname, ']' , ']]' ) + ']' , @d_dbname = '[' + Replace (@d_dbname, ']' , ']]' ) + ']' --复制存储过程信息到临时表 CREATE TABLE #sys_syscomments_bak ( name SYSNAME, xtype CHAR (2), number SMALLINT , colid SMALLINT , status SMALLINT , ctext TEXT ) --select o.name,o.xtype,c.number,c.colid,c.status,c.ctext into #sys_syscomments_bak --from dbo.syscomments c,dbo.sysobjects o where 1=2 EXEC ( 'insert #sys_syscomments_bak(name,xtype,number,colid,status,ctext) select o.name,o.xtype,c.number,c.colid,c.status,cast(c.ctext as varchar(8000)) from ' +@s_dbname+ '.dbo.syscomments c,' +@s_dbname+ '.dbo.sysobjects o where c.id=o.id and o.status>=0 and o.xtype=' 'P' ' and not exists( select * from ' + @d_dbname+ '.dbo.sysobjects where name=o.name)' ) --创建存储过程 DECLARE tb CURSOR local FOR SELECT 'use ' + @d_dbname + ' exec(' 'create proc dbo.[' + Replace ( name , N ']' , N ']]' ) + '] as --' ') exec sp_recompile [' + Replace ( name , N ']' , N ']]' ) + ']' FROM #sys_syscomments_bak DECLARE @s NVARCHAR(4000) OPEN tb FETCH tb INTO @s WHILE @@fetch_status = 0 BEGIN EXEC (@s) FETCH tb INTO @s END CLOSE tb DEALLOCATE tb --复制存储过程结构 EXEC Sp_configure 'allow updates' , 1 --reconfigure with override BEGIN TRAN EXEC ( 'delete c from ' +@d_dbname+ '.dbo.syscomments c,' +@d_dbname+ '.dbo.sysobjects o,#sys_syscomments_bak ob where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype insert ' +@d_dbname+ '.dbo.syscomments([id],[number],[colid],[status],[ctext]) select o.[id],ob.[number],ob.[colid],ob.[status],cast(cast(ob.[ctext] as varchar(8000)) as varbinary(8000)) from ' +@d_dbname+ '.dbo.sysobjects o,#sys_syscomments_bak ob where o.name=ob.name and o.xtype=ob.xtype' ) COMMIT TRAN EXEC Sp_configure 'allow updates' , 0 --reconfigure with override go |