My friend and SQL Server MVP, Pinal has wrote a wonderful post warning people who rename their stored procedures, functions, views and triggers. You can find his post here.

The problem is that, when you rename a stored procedure, function, view or trigger, the name in [sys].[syscomments] table is not updated. Hence if you try to read the definition using sp_helptext or OBJECT_DEFINITION() you will get the definition of the stored procedure with the old name.

Please read pinal's post to understand the problem in detail.

I was wondering why I see the correct definition when I try to modify a procedure using Sql Server Management Studio after renaming it. I did some investigation and here is what I found out.

Let us create a stored procedure.

CREATE PROCEDURE TestProc
AS
SELECT 'Hello'
GO

Now, locate this stored procedure in the management studio object list and rename it to "NewTestProc".

Let us see if the name is updated correctly.

sp_helptext TestProc
/*
Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'TestProc' does not exist in database 'xmlworkshop' 
or is invalid for this operation.
*/

That shows that the object is successfully renamed. Now let us try to retrieve the text of the modified procedure.

sp_helptext NewTestProc
/*
Text
---------------------------
CREATE PROCEDURE TestProc
AS
SELECT 'Hello'
*/

Note that the name of the procedure in the body. It is still "TestProc". Another way to retrieve the definition of a stored procedure, function, view or trigger is to use the OBJECT_DEFINITION() function.

SELECT OBJECT_DEFINITION(OBJECT_ID('NewTestProc')) AS body
/*
body
------------------------------------------------------------
CREATE PROCEDURE TestProc
AS
SELECT 'Hello'
*/

The result is the same in this case too. Let us now try to read directly from the system catalog view.

SELECT text FROM sys.syscomments
WHERE id = OBJECT_ID('NewTestProc')
/*
text
-------------------------------------
CREATE PROCEDURE TestProc
AS
SELECT 'Hello'
*/

So, if the name of procedure in the procedure body does not get updated, how does SQL Server execute the correct procedure? Well, the name of the procedure in the system metadata is updated, but it is not updated in the procedure definition. When you execute a stored procedure, SQL Server finds the ObjectID of the stored procedure and retrieves the procedure body using the ObjectID and executes the body of the procedure definition.

So, what is the problem in renaming?

We see that even after renaming a stored procedure, function, view or trigger, SQL Server can correctly identify and execute the correct procedure definition. So what is the problem in renaming?

Well, I see one problem with this approach. Most developers I work with, use the Management Studio to make modifications to the definition of a stored procedure, view, function, trigger etc. They expand the tree node of management studio, locate the correct object, right click and select the 'modify' or 'generate alter script' menu which will open the object definition in a new query window. Then they make modifications and execute the script to save their changes.

But in the past, I worked with a few developers who did not like to locate the object in the management studio and edit it. Instead, they quickly used sp_helptext to retrieve the body of the object, make their modifications and execute the script to save their changes. I see that people doing sp_helptext to retrieve the body of the object and make modifications should be very careful. After they save the changes, the new stored procedure will not be updated with the new definition. Instead, a new procedure will be created with the old name and new definition.

A few years back, I used to tell people not to use sp_helptext to edit/modify the definition of stored procedures, views, functions and triggers. The reason was that the version of sp_helptext in SQL Server 2000 did not preserve the formatting of the TSQL code. So after, making the modifications you will loose all the formatting, indentations etc. But the version of sp_helptext in SQL Server 2005 preserves the formatting and hence I stopped insisting people not to use sp_helptext.

But now I have one more reason for suggesting not to use sp_helptext for making modifications to procedures, views, triggers and functions etc.

How does Management Studio handles this?

It may be interesting to investigate how Management Studio handles this. If you generate the script of the stored procedure using Management Studio you will receive the correct definition. You can edit a stored procedure by selecting "modify" option from the Management Studio or by generating create/alter script.

Note that the script generated by Management Studio contains the correct definition of the procedure (with the new name).

USE [xmlworkshop]
GO
/****** Object:  StoredProcedure [dbo].[NewTestProc]    
Script Date: 09/28/2008 11:56:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewTestProc]
AS
SELECT 'Hello'

How does Management Studio retrieve the correct object definition? Let us investigate it. Let us run SQL Server profiler and see what query is being executed by Management Studio when we try to generate the Modify Script of the object.

If you run profiler to see what query SSMS runs to retrieve the definition of the object, you will find the following.

exec sp_executesql N'SELECT
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)
and(sp.name=@_msparam_3 
and SCHEMA_NAME(sp.schema_id)=@_msparam_4)'
,N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),
@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',
@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',
@_msparam_3=N'NewTestProc',@_msparam_4=N'dbo'

/*
Definition
-------------------------------
CREATE PROCEDURE TestProc
AS
SELECT 'Hello'
*/

Note that SSMS also receives the same definition as what we saw in our examples. But Management Studio is very smart and it modifies the definition and presents the correct definition to us.

It is also interesting to note that, after you save the ALTER PROCEDURE script from SSMS the object name is correctly updated in the body. Run the code generated by SSMS to alter the procedure.

USE [xmlworkshop]
GO
/****** Object:  StoredProcedure [dbo].[NewTestProc]    
Script Date: 09/28/2008 11:56:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[NewTestProc]
AS
SELECT 'Hello'

After you run the update script, run sp_helptext again to retrieve the new object definition.

sp_helptext NewTestProc
/*
Text
---------------------------------------
CREATE PROCEDURE [dbo].[NewTestProc]
AS
SELECT 'Hello'
*/

Note that the name of the procedure is updated correctly in the object definition after we run the update script.

This indicates that it is not recommended to rename stored procedures, views, functions and triggers. If you want to rename them, drop the object and recreate it with the modified definition.

If the object is replicated, SQL Server will not allow you to drop it and recreate it. If you try to drop the object, you will get the following error.

Msg 3724, Level 16, State 2, Line 1
Cannot drop the procedure 'NewTestProc' because it is being used for replication.

So if the object is replicated, you don't have the option to drop it and recreate. In such a case you have two options.

1. Remove the object from the articles, drop the object, recreate it and add it back to the replicated article list.
2. Use sp_rename to rename the object. After renaming the object, generate ALTER script from Management Studio and execute it to make sure that the object name is correctly updated in the body of the procedure, trigger, function, view etc.

SUMMARY:

1. Avoid renaming objects.
2. If you want to rename an object, drop the object and create it again with the new definition.
3. Do not use sp_helptext to retrieve the object definition and modify it.
4. Use sp_rename to rename an object only if the object is replicated. In such a case, after you rename the object, generate the ALTER script from SSMS and execute it once to make sure that the object name is correctly updated in the object body.