Copy one procedure from one database to another

,

The procedure copies one stored procedure from one database to another one

USE master
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp_CopyStoredprocedure')
  DROP PROCEDURE sp_CopyStoredprocedure
GO
CREATE PROCEDURE dbo.sp_CopyStoredprocedure 
  (
   @procedure sysname
  ,@sourceDb  sysname
  ,@targetdb  sysname
  )
AS
/*******************************************************************************

  Written By  : Simon Sabin
  Date        : 25 October 2002
  Description : Copies a stored procedure from one database to another
              : 
  History
  Date       Change
  ------------------------------------------------------------------------------
  25/10/2002 Created
*******************************************************************************/

DECLARE @lines int
DECLARE @line int
DECLARE @vline varchar(4)
DECLARE @objectid varchar(10)

DECLARE @DECLARE varchar(8000)
DECLARE @SELECT  varchar(8000)
DECLARE @EXECUTE varchar(8000)
DECLARE @PRINT   varchar(8000)
DECLARE @SQL     nvarchar(4000)

SET @SQL = 'USE ' + @sourcedb + '
SELECT @objectid = id, @lines = count(*) 
  FROM ' + @sourcedb + '.dbo.syscomments
 WHERE id = object_id(@procedure)
 GROUP BY id'

EXEC sp_executesql @SQL, N'@objectid int OUTPUT, @lines int OUTPUT, @procedure sysname', @objectid OUTPUT, @lines OUTPUT, @procedure
SELECT @objectid , @lines , @procedure, @SQL

SET @DECLARE = ''
SET @SELECT  = ''
SET @EXECUTE = ''
SET @PRINT   = ''

SET @line = 0
WHILE @line <= @lines
  BEGIN
    SET @vline = @line
    SET @DECLARE = @DECLARE + char(10) + 'DECLARE @line' + @vline + ' nvarchar(4000)'
    SET @SELECT  = @SELECT  + char(10) + 'SELECT  @line' + @vline + ' = text FROM ' + @sourcedb + '.dbo.syscomments WHERE colid = ' + @vline + ' AND id = ' + @objectid 
    SET @EXECUTE = @EXECUTE + '+ @line' + @vline 
 
    SET @PRINT   = @PRINT   + char(10) + 'PRINT @line' + @vline 
  
    SET @line = @line + 1  
  END

SET @DECLARE = @DECLARE + CHAR(10)
SET @SELECT  = @SELECT  + CHAR(10)
SET @EXECUTE = 'USE ' + @targetDb + char(10) + 'EXECUTE (''''' + @EXECUTE + ')' 

SET @SQL = 'IF EXISTS (SELECT 1 FROM ' + @targetDB + '.dbo.sysobjects WHERE name = @procedure )
  DROP PROCEDURE ' + @procedure
exec sp_executesql @SQL, N'@procedure sysname ', @procedure

PRINT @DECLARE 
PRINT @SELECT 
PRINT @EXECUTE
PRINT @PRINT
EXECUTE (@DECLARE + @SELECT + @EXECUTE + @PRINT)

GO

Rate

5 (2)

Share

Share

Rate

5 (2)