Quickest/Easiest way to use Search/Replace through all stored procedures

Question

Actually, this is a 2 part question.

1. Is it possible to use some sort of functionality to search through every stored procedure for a string and possibly replace it, like a standard Find/Replace function?

2. If you have all your stored procedure code include the full database path like this [db1].[dbo].[table1] and you change the database name to [db2] is there a way for SQL Server to automatically update all the code from [db1] tables to [db2]? Or does it have to be done manually?

Answer 1

From the Object Explorer Details window in SSMS, open the stored procedures folder. Select all the objects (you can multi-select from this window, which is pretty much the only purpose of the Object Explorer Details window) and right click, choosing to script as DROP and CREATE. You can now do a search/replace on this, replacing all you need in one go before executing it.

Edit: I've blogged about this solution.

Answer 2

Late one but hopefully useful.

There is a free search tool from ApexSQL that can find and rename objects in database.

They say it has a smart rename option that will find/replace all occurrences of some object such as table, function or stored procedure.

I have to add that I haven’t used the rename functionality but I can confirm that search is working quite well.

Also I’m not affiliated with ApexSQL but I do use their tools.

Answer 3

To search: if you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use?

This tool doesn't support replacing text, however - but even just being able to find all the relevant stored procedures (or other DB objects) is very helpful indeed!