Search and replace content in stored procedures

Question

I realy dont know how I could have lived without searching the text of stored procedures Can you search SQL Server 2005 Stored Procedure content?.

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

EDIT

I use Red Gate SQL Search for searching now. Its nice but they should give me the update feature! Please!

Answer 1

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.

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 query of the definition of the procedure, (I would) use it as a derived table (I'll call it T1 in 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.