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?

Viscountess

there are free refactoring tools for SSMS that can 'smart' rename my_table, for example. eg. apexsql. – Minutes

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

Negligee

Answer 2

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

2. Script them out with an "ALTER" instead of "CREATE" Press CTRL-H (find and replace)

3. Execute the script.

Earwig

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

Welcome to database development – Earwig

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

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

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 – Viscountess

which goes back to my previous point. script out the stored procedures, make your change once, and if needed make the changes manually in the future. You'll save yourself a lot of headache even though it seems like more work and counter intuitive. I've been doing db dev for over 15 years, so I've been around the block once or twice. – Earwig

Answer 3

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

Hamm

Answer 4

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

Golden

Answer 5

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.

Ahner

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

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