Journey to SQL Authority with Pinal Dave

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.

Example,

-          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

USE AdventureWorks
GO
sp_helptext sp_getemployee

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.

Conclusion

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://www.SQLAuthority.com)