复制存储过程

SQL语句复制存储过程 无 USE mastergoIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[sp_copyProce]') AND Objectproperty(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[sp_copyProce]goCREATE PROC Sp_copyproce @s_dbname SY

SQL语句复制存储过程

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