Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

A Search and Replace Script when Moving Objects from Prod to Test

By Ben Kubicek,


I had a situation where I wanted to copy a production database to test. Normally there is no issue here: just backup the database and restore it to test. The issue came up because there is an Integration database on production as well and there were a handful of custom stored procs and views that joined to this database. So the problem was that the test database needed these same joins pointed to the integration_test database and I did not want to have to manually change all the places these joins existed.


The first thing that came to mind was to use syscomments and use the text column to get the stored proc code or the code for the views. The issue with this is some of the stored procs are quite long and all of the code is not contained in the syscomments text column. So that led me to the sp_helptext proc. The sp_helptext proc returns all the lines for a stored proc no matter how long it might be.

So once I had the text of the object I was trying to fix I just needed to replace the text for prod with the text for test and then execute the script on the new database.


First I needed to create a temp table to insert the lines from sp_helptext.

create table #sp_helpText (
id int identity(1,1),
tmptext nvarchar(4000)
) insert into #sp_helptext exec sp_helptext @object_name

Next I loop through the lines that sp_helptext returns and replace the text. Note that I also need to change the Create Procedure or Create View to Alter Procedure or Alter View. You need to change the Create to an Alter so that when you execute this script at the end it will succeed.

...Start loop

select @tmptext = tmptext from #sp_helptext where id = @cnt
if @cnt <=4
select @t = @t + replace(replace(@tmptext,'CREATE ','ALTER '),@fromtxt,@totxt)
else begin
select @t = @t + replace(@tmptext,@fromtxt,@totxt)

...End loop

Finally you have the object text with the changed joins that will work in your new database you copied to test. The whole process would look something like:

--backup prod and restore to test.
use prod --This switches the database to the prod database
declare @t nvarchar(max)
exec pr_switch_something 'pr_storedproc1', 'integration','integration_test', @t output
use test
exec (@t)
use prod exec pr_switch_something 'v_customView1', 'integration','integration_test', @t output
use test
exec (@t)

NOTE: Since we are using a temp table to write the contents of sp_helptext you would not want the pr_switch_something proc to run at the same time as itself. The reason for this is the first time the proc is run it creates the temp table. If the proc gets called a second time while the first call is still running the call will fail when trying to create the temp table since the temp table already exists. A solution to this is to change the temp table to a normal table in the database and then add a sessionid to the table. Then for each call to the stored proc you get a unique sessionid and then limit your selects from the table by the sessionid.

For my use since I knew when I was coping over a database to test there was no issue with the stored proc being used twice at the same time.


I found myself in a situation where I needed to copy a prod database to test and I had some joins that pointed to another database that was specific to prod or test. I wanted to automate the process, so that no one needed manually change these joins after the copy to test was done. I found this solution worked well for me. I hope you find it useful too.


Total article views: 7294 | Views in the last 30 days: 1
Related Articles

Database Integrity Error

Database Integrity Error





Disabling SP_Helptext

How to disable SP_Helptext


Continuous Integration in a Development Environment using Red Gate Backup and SQL Compare

My co-worker and fellow DBA, Leigh Freijo has a blog entry about a Powershell script he created to r...


How Service Broker and .Net Integration happen?And how Queues are automatically created if record is added/modified in a table changed?

How Service Broker and .Net Integration happen?And how Queues are automatically created if record i...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place


Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones