Migrate stored procedures across databases
Copy stored procedure from one database to another with T-SQL
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
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
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