Search and replace content in stored procedures

Question

I realy dont know how I could have lifed without searching the text of stored procedures http://stackoverflow.com/questions/466130/can-you-search-sql-server-2005-stored-procedure-content.

The next big think for me would be the ability to replace some text in a couple of stored procedures. Is there a way to do this?

Answer 1

One good way of doing this is to start using Visual Studio Database Edition for your database schema management. This tool does many wonderful things, including refactoring your database code and doing search/replace activities across your schema.

The tool is free for users of Visual Studio Team Suite and Visual Studio Developer Edition.

Answer 2

Since the ROUTINE_DEFINITION column in INFORMATION_SCHEMA.ROUTINES is the full Create statement, you could simply do a find and replace on the routine definition, replace Create with Alter in the first part of the definition and pass the value into Exec(@revisedRoutineDefinition)

Answer 3

What's wrong with scripting the procedures, keeping the scripts in source control, and then using search and replace in a text editor to change the scripts?

Answer 4

you can select the procedures like below :

select name , definition from sys.procedures inner join sys.all_sql_modules on sys.procedures.object_id = sys.all_sql_modules.object_id

now you can make a update query for this.