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?

there are free refactoring tools for SSMS that can 'smart' rename my_table, for example. eg. apexsql. — g2server
– g2server, Commented Mar 10, 2015 at 11:37

Middle Class Lowlife · Accepted Answer · 2015-11-04 15:49:27Z

Here is an article outlining how to handle this using a cursor, and the sp_HelpText as mentioned above (including set as also mentioned).

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

marc_s · Accepted Answer · 2016-12-17 21:55:04Z

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...

Community · Accepted Answer · 2017-05-23 11:53:46Z

0

Find all stored procedures with a reference to that table (you can either use the dependencies stuff built into SQL Server or run a query looking for that table name see Search text in stored procedure in SQL Server)
Script them out with an "ALTER" instead of "CREATE" Press CTRL-H (find and replace)
Execute the script.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Mar 10, 2015 at 10:21

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

6 Comments

Phate01 Over a year ago

The script should be dynamic, because if I added a new procedure I'd have to edit the scritpt too

Jeremy Over a year ago

Welcome to database development

Jeremy Over a year ago

Also, if you added a new procedure, you'd have the right table name in it...right?

Jeremy Over a year ago

Doing this automatically is a BAD IDEA. You need to manually do this to ensure that it is correct. What if someone added a table named "my_table1" to the DB one day, and your script find the string "my_table" and replaces it with a different name, thereby invalidating your procedure.

Phate01 Over a year ago

You're right, but I'm sure at 100% that this isn't gonna happen, because this db is part of a software that my company is developing, and the new table that I have to replace exists only on the customer db

|

Dustin Bounty Hunter · Accepted Answer · 2024-07-08 10:13:44Z

DELIMITER // DROP PROCEDURE IF EXISTS SelectAllCustomers; CREATE PROCEDURE SelectAllCustomers() BEGIN SELECT * FROM customers_custom; END; // DELIMITER ;

pb. · Accepted Answer · 2015-03-10 10:20:40Z

-2

If sp_HelpText returns a table, why not you use a cursor to loop over the results and join the resulting strings together? It's nasty, but would do the trick.

answered Mar 10, 2015 at 10:20

pb.

4612 silver badges10 bronze badges

2 Comments

Phate01 Over a year ago

Doing EXEC sp_HelpText 'MyProcedure' will show the source code of the procedure, no table is created

pb. Over a year ago

Apologies - I meant a set of results rather than table.

Collectives™ on Stack Overflow

How can I edit a stored procedure?

5 Answers 5

Comments

Comments

6 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

6 Comments

Comments

2 Comments

Linked

Related