Rename Stored Procedures

Question

I observed the following function when renaming a stored procedure.

sp_RENAME 'User_Validate', 'sp_UserValidate' 

And when I do sp_helptext sp_UserValidate name of the procedure that I see

 CREATE PROCEDURE User_Validate (@userEmail nvarchar(200), @userPassword nvarchar(32)).... 

Why is the name not updated in the stored procedure?

But when I check

 select * from sys.procedures 

I find the name field is updated? What is the reason for this? The only logical conclusion I can draw is to better abandon the procedure and recreate with a new name.

edit 1: If I do sp_helptext User_Validate , it returns "The object" User_Validate "does not exist in the database" Process "or is invalid for this operation." But when I look at the stored procedure, the name User_Validate still exists.

Note. I know that renaming stored procedures is not good practice, the question I asked is out of curiosity.

Answer 1

sp_helptext does not process the procedure; it simply shows the original T-SQL batch that created the procedure, including comments and spaces.

Answer 2

sp_rename is not recommended for renaming stored procedures, views, triggers, and user-defined functions

Your conclusion is right, you must delete it and recreate it with a new name. Same thing in BOL - Rename Stored Procedure

You should also check the dependencies of the stored procedure , since renaming the stored procedure can lead to the failure of dependent objects if they are not updated in accordance with the change

Hope this helps