How can we help you today? How can we help you today?

Find and replace a reference in all database views

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.
KeithPresley
0

Comments

1 comment

Sort by
  • 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.


    Jon_Kirkwood
    0

Add comment

Please sign in to leave a comment.