How can I edit a stored procedure?

Question

I have several stored procedures in my database, structured like this:

CREATE PROCEDURE MyProcedure (.....)
AS
    DECLARE @myvar NVARCHAR(100);
    SET @myvar = (SELECT .... FROM my_table WHERE ....)
GO

I was asked to replace the table my_table in the FROM clause with another one in every procedure that has it.

I went through a lot of researches, but I should create a script that works by itself, and I haven't found anything suitable. For example I found the sp_helpTetx that shows the source code of a stored procedure, but is there a way to put it into a variable in order to edit it?

Answer 1

You can use tool like REDGATE SqlRefactor that works perfectly or you can script all the stored procedures, replace CREATE command with ALTER and then apply the other REPLACE in text you need...

I do it lot of time, you have to pay attention but it works...