Duplicate all stored procedures with a new name

Question

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;

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