Migrate stored procedures across databases

Copy stored procedure from one database to another with T-SQL

  • Share

When working on an app you create all your stored procedures, functions, tables, view and other objects in a development database (or t least it should be like that). A small but annoying problem occurs when you have to migrate your stored procedures to test or production database.

Imagine during development you had let's say 20 stored procedures. For each and every you have to scripti in dev database and execute in target database.

There are third party tools to do that, but they are usually not free or really bad, full of bugs and keep crashing.

The thing is that you can do this with a stored procedures by your self without involving 3rd party software in the flow.

The following solution is based on a simple query that reruns CREATE PROCEDURE script for stored procedures in a database.

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE '<procedure name>'
    

This query will return single or multiple records with SP code depending whether you used wild-card in a query (%) or exact term.

To test this we can create a simple stored procedure that returns current date time.

CREATE PROCEDURE [dbo].[spDateNow]
AS
BEGIN
     SELECT GETDATE() as [DateTimeNow]
END

    

If we execute the select query mentioned in the beginning we'll get the stored procedure code. Now all we have to do is to execute it in the target database. But before we execute we need to iterate through results to handle multiple records with code returned. For this we'll have to use cursor.

Now to wrap everything in a single SP and we end up with the following procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE MigrateStoredProcedure
	@ProcedureName VARCHAR(50),
	@TargetDatabase VARCHAR(50)
AS
BEGIN

DECLARE @GetCode CURSOR
DECLARE @Code NVARCHAR(MAX)

SET @GetCode = CURSOR FOR
SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE  @ProcedureName

OPEN @GetCode
FETCH NEXT
FROM @GetCode INTO @Code
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT

	-- GET SP CODE
    FROM @GetCode INTO @Code

	-- PREPARE FOR EXECUTING IN TARGET DATABASE
	SET @Code = @TargetDatabase + '.dbo.sp_executesql N' + CHAR(39)  + @Code + CHAR(39)

	--EXECUTE COMMAND
	EXEC sp_executesql @Code
END

CLOSE @GetCode
DEALLOCATE @GetCode

END
GO

    

To test it we'll need two databases and simple stored procedure which will be migrated in a source database or in our case dbDev database

Sql Server Tree

When we created test SP we'll have to create MigrateStoredProcedure database in a dbDev. Procedure receives procedure name and target database name so it is easily reusable for multiple case of migration or to migrate procedures not only to one specific database.

If we now execute the following SQL query in dbDev and expand Programmability section in dbTest we'll see that procedure spDate is there.

USE [dbDev]
GO

DECLARE	@return_value int

EXEC	@return_value = dbDev.dbo.MigrateStoredProcedure
		@ProcedureName = N'spDate%',
		@TargetDatabase = N'dbTest'

SELECT	'Return Value' = @return_value

GO
    

  • Share

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

Comments for this article

comments powered by Disqus