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.
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 (https://blog.sqlauthority.com)
20 Comments. Leave new
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. https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms188351(v=sql.90)
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.
Hi Pinal,
I did the same steps mentioned above and I can see the sp_rename updated sys.procedures. Is this issue related to any specific version of sql server. Need help on this!.
Thanks,
Suresh
You don’t mention the versions of SQL that you are running on.
really helpful…. what is drawback if its not updating name in ‘helptext’ file
thanks lot,thanks lot,thanks lot,thanks lot.God
bless u
Still a great post after all these years.
Hi, your article is great as always. But, I have one question, in case we need to rename a column in production which is being refernced by large number of views and stored procedure in multiple databases, can we automated the process of fixing all its dependencies? If yes, how. We are currently using SQL Server 2008 R2.