Rename a stored procedure in SQL Server

Question

I'm attempting to rename a stored procedure in SQL Server 2008 with sp_rename system sproc. The third parameter is giving me difficult though and I keep receiving the following error:

Msg 15249, Level 11, State 1, Procedure sp_rename, Line 75
Error: Explicit @objtype 'P' is unrecognized.

As the message indicates I'm passing in a P for the value of the parameter. I call the sproc like this:

EXEC sp_rename @objName = @procName, @newname = @WrappedName, @objtype = 'P';

I double checked the documentation which says this is the value from sys.objects. I ran the following to double check I wasn't going crazy

select * from sys.objects where name = 'MySprocName'

and indeed the type returned is P.

Does anyone know what I should pass here? I don't want to leave this empty since I'm creating a generic sproc to among other things rename arbitrary sprocs and if there is a name collision between a sproc and something else I don't want to have to worry about that.

Answer 1

According to the docs, 'P' is not a correct option. You should try 'OBJECT' as that seems like the closest thing to what you're trying to do. But, you should heed this warning ...

Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

Answer 2

Just omit the @objtype parameter (the default is null) and it will work.

EXEC sp_rename 'sp_MyProc', 'sp_MyProcName'

You will receive the following warning, but the procedure will be renamed

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

Like others stated, you should drop and recreate the procedure.

Answer 3

In SQL 2000 days, it was safer to DROP/CREATE-- SQL used to let the meta data for the proc get out of synch when you use sp_rename.

The best way to find out how to do fancy DDL like that is to use SSMS to rename the object while a profiler trace is attached.

Answer 4

sp_rename does not support procedures:

Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

Just create the new procedure with the same body and new name, then drop the old one.

Answer 5

I'm not sure about the @objtype variable, however I do know that renaming via sp_rename is bad.

When you create a stored proc, a record for it exists in sys.objects and the definition of the stored proc will be stored in sys.sql_modules.

Using sp_rename will only change the name in sys.objects, not in sys.sql_modules thus your definition will be incorrect.

The best solution is a drop & recreate