SQL query to find and replace text in a stored procedure

Question

How can I find and replace specific text in a stored procedure?

I have the find and replace query but I'm not sure how to post that back to the stored procedure or update the stored procedure with the query result.

Here is the query that I am using for the find and replace:

SELECT
   REPLACE (object_definition(object_id('storedprocedure_1')), 'findstring', 'replacestring')

Answer 1

Declare @spnames CURSOR
Declare @spname nvarchar(max)
Declare @moddef nvarchar(max)
Set @spnames = CURSOR FOR
select distinct object_name(c.id) 
from syscomments c, sysobjects o 
where c.text like '%findtext%'
and c.id = o.id
and o.type = 'P'    
OPEN @spnames
FETCH NEXT
FROM @spnames into @spname
WHILE @@FETCH_STATUS = 0
BEGIN   
    Set @moddef =
    (SELECT
    Replace ((REPLACE(definition,'findtext','replacetext')),'ALTER','create')
    FROM sys.sql_modules a
    JOIN 
    (   select type, name,object_id
    from sys.objects b
    where type in (
    'p' -- procedures
    )
    and is_ms_shipped = 0
    )b
    ON a.object_id=b.object_id where b.name = @spname)              
    exec('drop procedure dbo.' + @spname)
    execute sp_executesql @moddef
    FETCH NEXT FROM @spnames into @spname
END

This is what I was able to come up with, its currently doing the text replace and re creating the stored procedures.