Find and replace database name in views and stored procedures

Question

I'm in a bit of a predicament. There are over 1,000 views and stored procedures in a handful of databases that are hard-coded to go after another database.

In my example, the database names in SQL Server are HOST_1 and LOYALTY_1. The views and procedures are trying to go after HOST_PROD and LOYALTY_PROD in the FROM and JOIN statements. Is there any way that I can do a global find and replace on all the views and procedures to replace the PROD with 1?

There has to be an easier way than to right click on each one, create the alter script to a new window, change the text, and press F5. Even if I can create all of the necessary alters and run them at once, that would work.

Answer 1

This could be done using the sys.objects table and the OBJECT_DEFINITION() function.

SELECT OBJECT_DEFINITION(id) AS ORIGINAL, 
    REPLACE(OBJECT_DEFINITION(id),'VALUE_TO_BE_REPLACED', 'VALUE_TO_REPLACE_WITH') AS UPDATED
    ,*
FROM sys.sysobjects o
WHERE xtype = 'V'

The 'UPDATED' column from this query can be used to do a quick find and replace on whatever database or table name might be found in the view definition. You will want to make sure you do the same for every possible version of the text you are searching. For instance a database called 'db' might be written as [db] or db. Once you have what you want from the query just copy and paste to a query window and execute the whole thing. Please make sure to backup the database prior to running it. Also carefully proof read the output text so you can verify that the replace worked correctly and did not accidentally change more than you intended.

BTW, the answer WEI_DBA gave is perfectly acceptable also. It's just a different approach to the same thing. In his example, the GUI will aide in getting all of the object definitions to the query window. Then you would just use Find/Replace(ctrl+h) to replace the database name. To each his own in this case.
I don't know that there's an advantage in either approach except maybe that my approach will allow you to limit the query only to certain objects (views in this case) with additional filters added if needed in the where clause. Generate scripts will allow you to filter to only views, stored procs, etc, and then individually select objects but additional filters will be limited to whatever the UI can do.

Answer 2

The following would probably give you what you need:

SELECT 
    '-------------------------------------'  + CHAR(13) + CHAR(10) +
    'DROP ' + IIF(o.type = 'P', 'PROCEDURE', 'VIEW') + ' [' + s.name + '].[' + o.name + ']'  + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10) +
    REPLACE(REPLACE([definition],'HOST_PROD','HOST_1'),'LOYALTY_PROD','LOYALTY_1') + CHAR(13) + CHAR(10) +
    'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.[type] IN ('P','V')
AND [definition] LIKE '%HOST_PROD%' OR [definition] LIKE '%LOYALTY_PROD%'

This will only get the required procedures, will drop them before recreating them and accounts for funny named procedures/views. You could copy the result and paste to a new query window. Seems to work well on my test system.

Ensure you review this first though, you are responsible for your database and any damage done!