CODEWITHSUNDEEP
sqlsql-servert-sql
NoviceDeveloper
NoviceDeveloper

Reputation: 1290

SQL restore with cross referencing another database

When I restore a backup of MyDatabase_PROD to MyDatabase_TEST I have a cross Reference of another database in my TEST now that comes from prod.

For example a select in a view of

Select * from [My_DB_PROD].[dbo].[ID]

Insead of 

Select * from [My_DB_TEST].[dbo].[ID]

So now I have to write a separate script for each stored proc, view etc to fix that.

Is there a way to restore and run a single script to change all of those references? I was looking into db_name() or db_id if exists do something with it but I think there is a better way.

Any ideas?

Upvotes: 0

Views: 97

Answers (1)

john McTighe
john McTighe

Reputation: 1181

I would re-implement the Selects to other DBs as Local Synonyms.

That way you can have the same code in different regions.

Failing that - You could probably scan code for regular expressions with ?.?.? which would signal an external DB.

Or Another way is to Build up a list of prod DB Names (hopefully short)- then scan syscomments for these values& do a hard replace with the Dev values on the Dev DB

You can extract the text via a while loop vs syscomments- do a Replace() and Exec() the replaced SQL - Watch out for Giant sprocs that have multiple rows in syscomments

You'll probably need dbo or sa rights.

Edit to add SQL 

DECLARE @SQL varchar(max)

WHILE EXISTS (select * from sys.syscomments where text like '%My_DB_PROD%')

BEGIN


select top 1 @SQL = Text from sys.syscomments where text like '%My_DB_PROD%'

SET @SQL = REPLACE(@SQL, 'My_DB_PROD','My_DB_TEST')

EXEC (@SQL)

END

NB - This doesn't handle giant procs - you can check if you have any of these with:

select object_name(id) , count(*) from syscomments 

group by object_name(id)

having count(*) > 1

If you do then its a bit trickier as you have to concat all the separate text values into a single sql statement (colid 1,2,3 etc)

Check if this is the case - it might not be!

SQL for BIG Procs Here:

DECLARE @SQL varchar(max)='', @colid int=1, @ID Int, @MaxColID int, @TempSQL varchar(MAX)

WHILE EXISTS (select * from sys.syscomments where text like '%My_DB_PROD%')

BEGIN

    select top 1 @ID= ID from sys.syscomments where text like '%My_DB_PROD%'

    SELECT @MaxColID = MAX(COLID) from sys.syscomments where ID = @ID

    WHILE @ColID <= @MaxColID

    BEGIN

        SELECT @TempSQL = Text FROM syscomments where ID = @ID and colid = @Colid

        Set @SQL += @TempSQL

        SET @ColID +=1

    END



    SET @SQL = REPLACE(@SQL, 'My_DB_PROD','My_DB_TEST')

    EXEC (@SQL)

    SET @SQL = ''

END

Upvotes: 1

Related Questions