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

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.

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!

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.