下面是编程之家 jb51.cc 通过网络收集整理的代码片段。

编程之家小编现在分享给大家，也给大家做个参考。

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  

以上是编程之家(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。