Introduction

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.

Solution

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.

Code

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 
 begin
 select @t = @t + replace(replace(@tmptext,'CREATE ','ALTER '),@fromtxt,@totxt)
 end
 else begin
 select @t = @t + replace(@tmptext,@fromtxt,@totxt)
 end

...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.

Conclusion

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.