1

I want to take all stored procedures and create them under a new name. My problem is with procedures that are called from other procedures .

To rename a procedure is no problem for me. But to change/duplicate the procedures that still reference them by their old names is a problem.

For example:

CREATE PROCEDURE dbo.getdata 
AS
BEGIN
   EXECUTE dbo.up_updatetable ;
END;
6
  • Please edit your question to reflect what database server you're on. – Daniel Hutmacher Apr 7 '16 at 5:25
  • If you're using Visual Studio and TFS to check in your code, you might be able to use a feature called "refactoring". – Daniel Hutmacher Apr 7 '16 at 5:28
  • Is there a tsql because I do not have it .? – itzik Paz Apr 7 '16 at 5:32
  • I think your best bet is to script everything and change it by hand. – Daniel Hutmacher Apr 7 '16 at 5:34
  • We are talking about ~600 procedures. – itzik Paz Apr 7 '16 at 5:35
1

How about such a little script, to generate new scripts for all procedures, replacing all old names with new names (definition and calls):

SELECT 
    O.Name as ProcName 
    ,REPLACE(REPLACE(REPLACE(
        M.Definition, 
            'old_proc_name_1', 'new_proc_name_1'),
            'old_proc_name_2', 'new_proc_name_2'),
            'old_proc_name_3', 'new_proc_name_3')
        -- etc.
as CreateScript 
FROM sys.sql_modules as M INNER JOIN sys.objects as O 
ON M.object_id = O.object_id 
WHERE O.type = 'P'
and O.name not like 'sp%' -- exclude special system procs

Then you can execute all the scripts, either by hand, or using a cursor (more prefered for hundreds of procs).

But begin with checking carefully at least a couple of scripts before executing them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.