I have written many articles about renaming a tables, 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.
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.
CREATE PROCEDURE sp_Employee
SELECT * FROM dbo.Employee
WHERE FName LIKE '%i%'
ORDER BY EMPID
– 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 (http://blog.SQLAuthority.com)