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 difficulty 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

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 2

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.

Also (from the same MSDN page):

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.