How to edit a stored procedure?

Question

I have several stored procedures in my database, structured as follows:

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

I was asked to replace the my_table in the FROM different one in every procedure that has it.

I went through a lot of research, but I have to create a script that works on its own, and I did not find anything suitable. For example, I found sp_helpTetx , which shows the source code of a stored procedure, but is there a way to put it in a variable to edit it?

Answer 1

You can use a tool like REDGATE SqlRefactor, which works fine or you can script to store all stored procedures, replace the CREATE command with ALTER, and then apply another REPLACE in the text you need ...

I do this a lot of time, you should pay attention, but it works ...

Answer 2

• Find all stored procedures with a link to this table (you can either use the dependency material built into SQL Server or run a query looking for this table name, see Search text in a stored procedure in SQL Server )

• Script using "ALTER" instead of "CREATE" Press CTRL-H (find and replace)

• Run the script.

Answer 3

Here is an article that describes how to handle this with the cursor, and sp_HelpText, as described above (including setting as mentioned above).

http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures

 -- set "Result to Text" mode by pressing Ctrl+T SET NOCOUNT ON DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100) -- text to search for SET @searchFor = '[MY-SERVER]' -- text to replace with SET @replaceWith = '[MY-SERVER2]' -- this will hold stored procedures text DECLARE @temp TABLE (spText VARCHAR(MAX)) DECLARE curHelp CURSOR FAST_FORWARD FOR -- get text of all stored procedures that contain search string -- I am using custom escape character here since i need to espape [ and ] in search string SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ' FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\' ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ' OPEN curHelp FETCH next FROM curHelp INTO @sqlToRun WHILE @@FETCH_STATUS = 0 BEGIN --insert stored procedure text into a temporary table INSERT INTO @temp EXEC (@sqlToRun) -- add GO after each stored procedure INSERT INTO @temp VALUES ('GO') FETCH next FROM curHelp INTO @sqlToRun END CLOSE curHelp DEALLOCATE curHelp -- find and replace search string in stored procedures -- also replace CREATE PROCEDURE with ALTER PROCEDURE UPDATE @temp SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith) SELECT spText FROM @temp -- now copy and paste result into new window -- then make sure everything looks good and run GO 

Answer 4

If sp_HelpText returns a table, why don't you use the cursor to process the results and combine the resulting rows? This is nasty, but will do the trick.