SQL Server replace source objects with synonyms

I have about 100 - 150 stored procedures. I need to quickly change references to all external databases with synonyms. I have e.g. these lines of code in my procedures:

 select   column1, column2
 from     OtherDb.SomeSchema.SomeTable    

or

 exec SOMEOTHERSERVER.OTHERDB.dbo.DOSOMETHING    

and need to replace it with

 select   column1, column2
 from     dbo.FirstSynonym

and

 exec  dbo.DoSomethingSynonym

Manually opening all these procedures would tak开发者_如何转开发e me considerable amount of time. Any idea how to achieve this quickly?

Thanks, Petr

Use bcp or sqlcmd with this query to generate a series of ALTERs

SELECT
    REPLACE(
        REPLACE(definition,
               'SOMEOTHERSERVER.OTHERDB.dbo.DOSOMETHING',
               'dbo.DoSomethingSynonym'
               )
           'OtherDb.SomeSchema.SomeTable',
           'dbo.FirstSynonym'
           ) + '
GO
'
FROM
    sys.sql_modules

You can add another REPLACE to change CREATE PROC to ALTER PROC if desired

You could do the following:

• Use the Tasks->Generate Scripts feature of SSMS to generate a file with CREATE PROCEDURE statements and all your stored procedure code.
• Modify this file via Search/Replace or the ViEmu features to match your desired state.
• Delete all existing stored procedures by using the Object Explorer Details Window
• Recreate everything from the file you generated/modified.
• (please test a modification like this on a test environment first)

I prefer gbn's solution, but I'll describe how I solved the job:

1. Iterate procedures, views and UDF using SMO (C#)
2. For each procedure, iterate all synonyms (also using SMO)
3. If procedure script contains base object names, replace them with synonyms, otherwise skip the procedure
4. return script only for procedures where changes are needed

It is also possible to directly execute the alter script from C# for each edited object, but I prefer to see the script before executing.

For obvious reason, when replacements are proceeded, synonyms have to be ordered by their base_object_name length in descending order.