SQL SERVER – Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures

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

SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures sp_createProcedure

–          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'

SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures sp_rename

–          Use sp_helptext to see the stored procedure

USE AdventureWorks
GO
sp_helptext sp_getemployee

SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures sp_helptext

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.

SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures error

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)

, , ,
Previous Post
SQL SERVER – Puzzle – Computed Columns Datatype Explanation
Next Post
SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation

Related Posts

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)

    Reply
  • 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.

    Summary of the article must be memorized by any developer.

    Regards,
    Pinal

    Reply
  • 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

    Reply
    • Hello Abhishek.
      I perform the same as you mentioned above, I found the procedure name is updated in sys.procedures.

      Reply
  • Alireza Haghshenas
    January 10, 2009 4:21 pm

    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

    Reply
  • 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.

    Reply
    • You can make use of ALTER procedure command

      ALTER procedure sp_jagdish
      as
      your select statement here

      Reply
  • 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.

    Reply
  • Thanks for the information, very useful. Wouldn’t of thought of this.

    Reply
  • hi sir
    i think when we rename a stored procedure it is updated in sys.procedure. but it is not updated in syscomments..

    Reply
  • What is the drawback or issue if the name is not updated in sys.procedures?

    Reply
  • 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.

    Reply
  • This is really helpful… Thank u Pinal Sir. Cheers!!! :)

    Reply
  • Thanks a lot for this wonderful information, sometimes we take it so casually to rename a store procedure.

    Reply
  • 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

    Reply
  • really helpful…. what is drawback if its not updating name in ‘helptext’ file

    Reply
  • thanks lot,thanks lot,thanks lot,thanks lot.God
    bless u

    Reply
  • Still a great post after all these years.

    Reply
  • Bhawna Aggarwal
    October 3, 2016 5:00 pm

    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.

    Reply

Leave a Reply

Menu