Change Links in SQL Objects when Cloning Production to Test

Let me say at least one time in this article…..”Backup your Database before running the created text file.” Ok, now that I have said that let’s talk about a problem that occurs a lot, especially if you are working in multiple databases. If you have databases for Production and Test and you clone regularly then you know that sometimes you have to update stored procedures and views if they are linked to other production and test databases.

For example. Suppose you have four databases. Two production and Two Test. In your production databases you have one for all of your HR information while the other is your Payroll information. So you have something like the chart below. (see Figure 1)

image1
Figure 1

Now when you clone your production HR database to test your links for the new test instance are pointing to the production instance of Payroll (Figure 2). This means that you have to open up the newly cloned test database and point the views and procedures to the test HR database see below. Sometimes this can be very time consuming. I developed this script to help me with this issue.

image2
Figure2

Use [TestDatabase]
Go

Declare @CurDatabaseName varchar(40)
Declare @NewDatabaseName varchar(40)
Declare @sql varchar(3000)
Set @CurDatabaseName = 'NameOfDatabaseToLookFor' --Look for this in the Views/Procedures
Set @NewDatabaseName = 'NameofDatabasetoReplaceWith' --Replace it with this Value

DECLARE @sObjectName varchar(80)
DECLARE @sObjectType varchar(80)
DECLARE @sDefinition varchar(MAX)
DECLARE @sModifiedDef varchar(MAX)

--Create Temp Table
CREATE TABLE ##tbl
(
ObjectName varchar(80) NOT NULL,
ObjectType varchar(80) NOT NULL,
ObjCurDefin varchar(max) Not NULL,
ObjNewDefin varchar(max) not Null
)

Insert Into ##tbl
SELECT
b.Name AS [ObjectName],
CASE WHEN b.type ='p' THEN 'Stored Procedure'
WHEN b.type ='v' THEN 'View'
ELSE b.TYPE
END AS [ObjectType]
,a.definition AS [Definition]
,Replace(Replace((REPLACE(definition,@CurDatabaseName,@NewDataBaseName)),
'CREATE ','ALTER '),'''','''') AS [ModifiedDefinition]
FROM sys.sql_modules a
JOIN
( select type, name,object_id
from sys.objects
where type in (
'p' -- procedures
,'v' --views
)
and is_ms_shipped = 0
)b
ON a.object_id=b.object_id
INNER JOIN
(
SELECT Name, 'p' as Type FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%' + @CurDatabaseName + '%'

Union All
sELECT NAME, 'v' as Type FROM SYS.VIEWS
WHERE OBJECT_DEFINITION(object_id) LIKE '%' + @CurDatabaseName + '%'
) fd on b.Name = fd.Name and b.type=fd.type
order by b.name

Select * from ##tbl

select @sql = 'bcp "' +
'select ' +
'ObjNewDefin + CHAR(13)+CHAR(10) + ''GO'' as action ' +
' from ##tbl ' +
'order by ObjectName" queryout C:\New_Query.txt -c -T '

exec master..xp_cmdshell @sql

DROP TABLE ##tbl

The net result of this process is that it will first return all views and stored procedures where it finds the @CurDatabaseName. Once they are found then the @CurDatabaseName is replaced with @NewDatabaseName. Once this is done then it creates a file called New_Query and adds all of these script changes into one file. Upon completion of this then you can open up the script in a query window and run it in the Test instance.

Hope you find this helpful and remember to always backup your database before running this. Running this in the test environment has very limited downsize risk. If it fails you just reclone production.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 − = 6

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>