SQL restore with cross referencing another database

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