XPSCodes

I wrote a small stored procedure to copy / promote stored procedures from one database to another on the same SQL Server. This is particularly useful when you have to promote stored procedures from TEST to production etc.

Promoting the stored procedures from source(i.e. Test)

1. Create this stored procedure in source database. Please not the target database name here. It needs to be changed to your target database.

Create Procedure PromoteSP
( @ProcName varchar(100))
As
Begin
Declare @Procedure Varchar(max)
Declare @RC Int
Select @Procedure = Replace((Select SysComments.Text as “data()”
From SysComments Inner Join Sysobjects on SysObjects.ID = SysComments.ID Where Sysobjects.name=@ProcName For XML PATH(”)),’ ‘,”)
IF @Procedure IS NOT NULL
Execute @RC = [TargetDB].[Dbo].[ExecuteScript] @Procedure

Print @RC
End

2. Create this on the target database

Create Procedure [Dbo].[ExecuteScript]

(
@Procedure varchar(max)
)
As
Begin
Execute(@Procedure)
End

Now to promote a stored procedure from source to target, run this in source database.

PromoteSP ‘usp_sample’

This will copy stored procedure ‘usp_sample’ from source database to target.

Getting the stored procedures from Target:

The same thing can be done from target database as well. Please use the below stored procedures to achieve that.

1. Create this in Target database.

Create Procedure PromoteSPFromProd
( @ProcName varchar(100))
As
Begin
Declare @Procedure Varchar(max)
IF @ProcName IS NOT NULL
Select @Procedure = [SourceDB].[Dbo].[GetSPScript](@ProcName)
If @Procedure IS NOT NULL
Execute @Procedure
End

2. Create this in Source Database

Create Function [Dbo].[GetSPScript]
(
@ProcName varchar(100)
)
Returns Varchar(max)
As
Begin
Declare @Procedure Varchar(max)
Select @Procedure = Replace((Select SysComments.Text as “data()”
From SysComments Inner Join Sysobjects on SysObjects.ID = SysComments.ID Where Sysobjects.name=@ProcName For XML PATH(”)),’ ‘,”)
Return @Procedure
End

To copy stored procedure ’sup_sample’ from source to target, run this query in target database.

PromoteSPFromProd ‘usp_sample’

This is a very basic version without any error checking. There is a lot of room for enhancements. For example, If the stored procedures already exist in Target database, we can add checks to see if it exists and drop it before running the query.

written by XPSCodes