Rename a stored procedure

[susanma]

We need to apply some naming standards to existing databases and are trying to rename all the objects. Stored procedures are the problem, I can rename the object using sp_rename but this does not change the name within the procedure i.e. after CREATE PROCEDURE. Does anyone know of a quick way to do this please?

 

[ClaireHCM]

Why it doesnt work for the name of stored procedrues??
What error you are getting?

 

[susanma]

sp_rename works and does not issue any error. It updates the entry in sysobjects but does not edit and change the name held inside the stored procedure following the CREATE PROCEDURE text! I am asuming that it is not designed to do so!

 

[SQLSister]

I have noticed this too. The worst thing is if you aren't aware of this, you can go to change a script that has been renamed and then it will try to run against the old name not the new one. What I do if I need to rename is generate the script as create procedure, change the name in the script, run it and then delete the old unneeded script. I can see where this would be a pain for every stored procedure though!

Don't forget that any references to your stored procedures in your code will need to be changed as well.

 

[SonOfEmidec1100]

My solution was to use Enterprise Mngr to generate a script file containing a create only (no drop) for all the stored procs to be changed. Used notepad to do some global modifications then ran the script to create the 'new' procs. External references to the stored procs was the real problem, but because the originals were still there these references were changed progressively, and the old stored procs deleted.