I have written many articles about renaming a table, columns, and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. Let us learn about how renaming stored procedure does not update sys.procedures.
The interesting fact is that when we rename a stored procedure using SP_Rename command, the Stored Procedure is successfully renamed. But when we try to text the procedure using sp_helptext, the procedure will be having the old name instead of new name.
– Create a Stored Procedure in AdventureWorks Database named SP_Employee.
USE AdventureWorks GO CREATE PROCEDURE sp_Employee AS SELECT * FROM dbo.Employee WHERE FName LIKE '%i%' ORDER BY EMPID GO
– Rename the Stored Procedure SP_Employee to SP_Getemployee
After Creating Stored Procedure, now we want to rename a stored procedure. So using sp_rename we can change the name of Stored Procedure as shown below:
sp_rename 'SP_Employee', 'SP_GetEmployee'
– Use sp_helptext to see the stored procedure
We can see the name of the stored procedure is the old name and not new name but when we try to access the old name there is an error that sp not found.
This happens because when the store procedure is renamed, the sys.procedures system table is not getting updated. The only solution to this is to drop the stored procedure and re-create it with the new name.
Reference : Pinal Dave (https://blog.sqlauthority.com)