Maintaining multiple named environments with cross database objects

A common issue we have is maintaining multiple environments, when using cross database objects such as stored procedures, views and functions. An example would be having a source oltp db called Maindb, that gets pushed to different env's with the names of test_Maindb, staging_Maindb, and warehouse_maindb, etc. Now lets say there's a corresponding database for each of these databases called Secdb, test_Secdb, etc. Within the Secdb you might have stored procedures or views that reference the maindb such as the below statement.

create procedure exampleSP

as

select * from Maindb.dbo.table1

Now every time Maindb goes to a different environment the sproc needs to be recompiled to reference the new name of the db for example in test the sproc would look like this:

create procedure exampleSP

as

select * from test_Maindb.dbo.table1

This can become fairly cumbersome to change manually. To get around this the following script allows you change all the reference names of all the logical objects within the db.

Declare @orgDBName varchar(100)

Declare @NewDBName varchar(100)

set @orgDBName = 'Maindb'

set @NewDBName ='Test_Maindb'

Declare @FindQuery varchar(2000)

Declare @Stmt nvarchar(max)

set @FindQuery = 'select definition from sys.all_sql_modules where definition like ' + '''' + '%' + @orgDBName + '%' + ''''

+ ' and object_id > 0 '

create table #TempDef

(Def nvarchar(max))

insert into #TempDef

exec (@FindQuery)

update #TempDef set Def = REPLACE(def,'Create Procedure', 'Alter Procedure')

update #TempDef set Def = REPLACE(def,'CREATE VIEW', 'Alter View')

update #TempDef set Def = REPLACE(def,'CREATE FUNCTION', 'Alter FUNCTION')

update #TempDef set Def = REPLACE(def,@orgDBName + '.', @NewDBName + '.') from #TempDef

Declare UpdateCursor cursor for

Select def from #TempDef

Open UpdateCursor

Fetch Next from UpdateCursor

into @Stmt

While @@Fetch_status = 0

Begin

--print (@stmt)

exec (@stmt)

Fetch Next from UpdateCursor

into @stmt

End

Close UpdateCursor

Deallocate UpdateCursor