时间:2021-07-01 10:21:17 帮助过:37人阅读
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
- 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