Ok, so you have a typo in the name of your stored procedure (or any other procedural object in your SQL Server database). Or, maybe your stored procedure name does not fit the naming policy rules. Anyway, you want to change the name of this damn procedure immediately. The very first thought – use sp_rename system stored procedure (or simply press F2 while having the procedure selected in the Object Explorer window in SQL Server Management Studio). Stop! Do not rename your procedural objects!

And here is the reason why you should never ever use sp_rename.

Let’s create a dummy stored procedure in the tempdb system database:

USE tempdb;

GO

CREATE PROC dbo.SomeDummyProcedure

AS

GO

Now, let’s try to rename it:

EXEC sp_rename SomeDummyProcedure, SomeDummyProcedure_Renamed

Notice the warning message coming out:

Caution: Changing any part of an object name could break scripts and stored procedures.

Ok, now you know some objects can be broken. The job is done. Your procedure is renamed. Oh, really? Let’s see what is stored in sys.sql_modules catalog view:

SELECT *

FROM sys.sql_modules

WHERE [object_id] = OBJECT_ID('dbo.SomeDummyProcedure_Renamed');

The result is quite not what you would expect:

What the hell? The name of your procedure is changed but not its definition! It still contains the old name! Well, you may think that you should refresh the procedure:

EXEC sp_refreshsqlmodule 'dbo.SomeDummyProcedure_Renamed';

Again – a big surprise!

Msg 208, Level 16, State 6, Procedure sp_refreshsqlmodule_internal, Line 75
Invalid object name ‘dbo.SomeDummyProcedure’.

Quite interesting, huh? :-) It appears that sp_refreshsqlmodule stored procedure (used often to refresh views or to verify procedural objects during database migration) uses the object definition returned by sys.sql_modules catalog view. If you play for a while with some system objects you will find out that sp_helptext system stored procedure and OBJECT_DEFINITION system function (it’s used in sys.sql_modules to generate the definition column, and underneath there is an internal table sys.sysobjvalues that stores the object definitions as binaries) also return the definition with the old name of your procedure… What a shame. And – why am I not surprised? – this is by design. In Books Online they put some clarification:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

“But wait! There is this cool scripting option in the Management Studio tool!” – you could say. Well, yes, there is. But let’s look closer at what it does.

If you right-click the stored procedure in the Object Explorer or Object Explorer Details windows, you can script your procedure using Modify or Script Stored Procedure as options.

And, another surprise – both options return different T-SQL code!

-- This is what Modify option returns:
--------------------------------------

USE [tempdb]

GO

/****** Object:  StoredProcedure [dbo].[SomeDummyProcedure_Renamed]    Script Date: 02/02/2010 07:47:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[SomeDummyProcedure_Renamed]

AS

-- And this is what Script Stored Procedure As option returns:
--------------------------------------------------------------

USE [tempdb]

GO

/****** Object:  StoredProcedure [dbo].[SomeDummyProcedure_Renamed]    Script Date: 02/02/2010 07:48:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[SomeDummyProcedure_Renamed]

AS

GO

Do not pay attention to the comments. Compare the procedure definitions in both cases. Do you see these blank lines? But also – notice that there is one more difference if you compare the above definitions to the original one. The square brackets! Where do they come from??? Of course, the code will run with no error, but THIS IS NOT THE ORIGINAL DEFINITION OF YOUR PROCEDURE!!! And what if you have some object versioning system? What if you rely on the previous version’s definition or its checksum? Then scripting any procedural object with Management Studio can make some problems.

And one more thing – when you script with Management Studio, notice that CREATE and ALTER keywords are always uppercase, even if they are lowercased in the original object definition.

So, my final remarks are the following:

• do not ever use sp_rename to rename the procedural objects,
• consider disallowing the executions of sp_rename in your databases (fortunately the RENAME event for DDL trigger is implemented in SQL Server 2008),
• modify the T-SQL code returned by the scripting mechanism available in Management Studio if it’s critical to script object definition exactly as it was written when you created or lately altered the object.

If you agree with me that the way the scripting mechanism in Management Studio works is not good, see (and vote!) the following item on connect.microsoft.com:

SSMS: Scripting of procedural object changes object definition