Options
Find and replace a reference in all database views
KeithPresley
Posts: 1 New member
in SQL Prompt
Hi
Looking to change the reference to a database instance in ALL the views of another database.
i.e. [DatabaseInstance]
....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.
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.
Tagged:
Answers
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.