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://blog.SQLAuthority.com)
Nice post, Pinal. I know many people just rename stored procedures, functions, views etc right from the Management Studio and land up with this problem.
Just wanted to add that this problem exists with Views, Triggers and Functions too. http://msdn.microsoft.com/en-us/library/ms188351(SQL.90).aspx
Thanks Pinal for initiating this discussion. I found this bit interesting and investigated it further. I was curious how SSMS manages this, because when you generate the ALTER/CREATE script from SSMS it generates the correct object name.
I have posted my findings here: http://jacobsebastian.blogspot.com/2008/09/be-careful-when-renaming.html
I request all the readers of my article to read the article posted by Jacob here. It explains in depth the why this happens and how to avoid it.
http://jacobsebastian.blogspot.com/2008/09/be-careful-when-renaming.html
Summary of the article must be memorized by any developer.
Regards,
Pinal
Hi Pinal,
I read the article and tried to follow the same.
Though after executing sp_helptext, I saw that the name is not changed, but when i see in the sys.procedures, i found that the name of the procedure is changed i.e the sys.procedures system table do get updated.
Pls check and clarify if I am going wrong.
Thanks in advance.
Abhishek
Hello Abhishek.
I perform the same as you mentioned above, I found the procedure name is updated in sys.procedures.
Hi.
I open the procedure in Visual studio, and see the old name after the “Alter procedure” as you said. It’s not unexpected that saving this procedure fails. My solution was to change the name that comes after the “Alter procedure” to the new one and everything is fine ever since.
thanks,
Alireza
helo sir,
i was reading ur suggestion and try to solve my problems…
after creating the stored procedure ,if i want to delete that procedure so what can i do for that?
like–
create procedure sp_jagdish
as
select * from jagdish
using this procedure i want rename the one of column name, so how could i do this? If i want to delete this procedure so how could i do this? pls tell me the answer.
You can make use of ALTER procedure command
ALTER procedure sp_jagdish
as
your select statement here
Hi Pinal,
Nice and usefull artical. I have experianced this problem now. As you and jacob mentioned, its better to drop the object and recreate it.
Thanks for the information, very useful. Wouldn’t of thought of this.
hi sir
i think when we rename a stored procedure it is updated in sys.procedure. but it is not updated in syscomments..
What is the drawback or issue if the name is not updated in sys.procedures?
I think that name updated with respect to object id in SYS.procedures but for that same id text did not updated in syscomments table.
This is really helpful… Thank u Pinal Sir. Cheers!!! :)
Thanks a lot for this wonderful information, sometimes we take it so casually to rename a store procedure.