Find and replace a reference in all database views

KeithPresley

Hi 
Looking to change the reference to a database instance in ALL the views of another database.
i.e. [DatabaseInstance]

CREATE VIEW [schema].[ViewName]

AS

SELECT * FROM [DatabaseInstance].[database].[schema].[ViewName] 

....and save the action as a script/task, so I can use very time I restore a copy of the live database to the test server.

Regards,

Keith.

Jon_Kirkwood

Hi @KeithPresley

 Thanks for reaching out on the Redgate forums regarding your question.

I don't believe we have a solution that would conduct this completely, however we may get most of the way using SQL Compare.

 

You could use SQL Compare to generate an output of Views from your database into an scripts folder.

Then using a find/replace tool that can search through multiple files and update at once. I saw a few possible options here: https://www.raymond.cc/blog/search-a-particular-word-in-multiple-editable-text-files/

Then re-run SQL Compare using the scripts folder as a the source and then deploy to your source database, or executed manually as required.