复制存储过程

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

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"不存在',@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,cast(c.ctext as varchar(8000)) from '
    [email?protected]_dbname+'.dbo.syscomments c,'[email?protected]_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']]') + ']'
      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 '[email?protected]_dbname+'.dbo.syscomments c,'[email?protected]_dbname+
    '.dbo.sysobjects o,#sys_syscomments_bak ob      where c.id=o.id and o.name=ob.name and o.xtype=ob.xtype      insert '[email?protected]_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 '[email?protected]_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  

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!