Search and replace content in stored procedures

Question

I really don't know how I could live without searching for stored procedure text. Can you find the contents of a SQL Server 2005 stored procedure? .

The next big thing for me would be the ability to replace some text in several stored procedures. Is there any way to do this?

EDIT

I am using Red Gate SQL Search to search now. Its nice, but they should give me the opportunity to update! You are welcome!

Answer 1

You can choose the following procedures:

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

You can now request an update for this.

Answer 2

I know this is for SQL2005, but in SQL2008 you can do this:

 select name, object_definition(object_id) proceduredefinition from sys.procedures 

Now that you have a procedure definition request, I would use it as a view (I will call it T1 in the example below)

 select name, proceduredefinition, replace(proceduredefinition,'texttoreplace','withthistext') as newcodedefinition FROM T1 where t1.proceduredefinition like '%something to search for%' 

Now copy and paste the new code definition into SSMS and execute.

Answer 3

One good way to do this is to start using Visual Studio Database Edition to manage your database schema. This tool does a lot of great things, including refactoring your database code and performing search / replace operations on your schema.

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

Answer 4

What happened with writing the procedures, saving the scripts in the original control, and then using search and replace in a text editor to modify the scripts?