复制存储过程

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