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

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)

About these ads

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

  1. 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

  2. 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

  3. 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.

  4. 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.

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

  6. 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.

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  8. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s