36

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.

Improve this question

9 Answers 9

Reset to default
71

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.

Improve this answer
20

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.

Improve this answer
2
  • Thanks, I read that but somehow missed the proper value I needed to put in.
    – Peter Oehlert
    Commented Sep 3, 2009 at 20:34
  • Note that dropping of procedure will drop all associated permissions. The best way to rename is to use sp_rename and then alter proc to correct sys_modules.
    – Boogier
    Commented Sep 16, 2015 at 9:15
3

There are two approaches to rename stored procedure.

  1. Dropping and Recreating Procedure : The problem is it will lead to losing of permissions.
  2. sp_rename of Procedure : Permissions will remain intact. The stored procedure will be renamed. But, sys.sql_modules will still be having the old definition.

I prefer the second approach. I followed the below steps:

  1. Have copy of the stored Procedure content
  2. Rename the stored procedure
EXEC sp_rename 'dbo.OldStoredProcedureName','NewStoredProcedureName'
GO
  1. ALTER PROCEDURE to have the modified code of the procedure in the sys.sql_modules
ALTER PROCEDURE dbo.NewStoredProcedureName
...

Now, Stored procedure name is also updated and code is refreshed in sys.sql_modules and permissions are also intact.

Improve this answer
1
  • I see no benefit in your second approach over the recommended way of deleting and recreating the procedure; you still need to have the definition of the procedure at hand (your ellipses). This is the difficult part when you have a lot of procs to rename and want to generate a script to do it for you.
    – Rik Bradley
    Commented Mar 1 at 10:36
2

  • The third parameter(@objtype) is required when you want to rename a database object other than a stored procedure. Otherwise it is necessary to pass a third parameter. For example renaming a column using sp_rename will look something like this.

    USE AdventureWorks2012;  
GO  
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
GO

  • To rename a user-defined stored procedure using sp_rename,you can do something like this and it literally works

sp-rename

  • However it is not recommended by Microsoft, DB experts (and others here as well) to use this stored procedure as it will break things internally and might possibly incur unusual results later.Therefore it is bad. Here's what Microsoft has to say:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. 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.

Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.

You can read about it here: sp_rename (Transact-SQL)

Improve this answer
1

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

Improve this answer
1
  • For what I'm doing it doesn't actually matter. I'm doing some injection where I dynamically create a proc based on the original definition, rename the original to a new name, then call the original from my injected version. When I'm done with my instrumentation I undo the process.
    – Peter Oehlert
    Commented Sep 3, 2009 at 20:40
0

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.

Improve this answer
0

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.

Improve this answer
2
  • 1
    This isn't correct. sp_rename worked by specifying 'OBJECT' as the @objtype.
    – Peter Oehlert
    Commented Sep 3, 2009 at 20:34
  • 6
    @Peter: it may not be 100% accurate, but using sp_rename is certainly not recommended. "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." (from MSDN)
    – Valentino Vranken
    Commented Mar 22, 2012 at 13:10
0

All that Valentino Vranken said is true. However bear in mind that when you drop and create a stored procedure, you lose all of your metadata. (Create_Date, Modify_Date etc.)

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.

This is also true. However I found that when you run the alter script after making the rename, it corrects the name in the definition in the modules.

I have wondered how the SSMS interface does all of this automagically without using T-SQL. (Perhaps it runs an alter script each time you rename an SP using the interface? I don't know.) It would be interesting is MS were to publish what is happening behind the scenes when you do a SSMS rename.

Improve this answer
1
  • SSMS (Sequel Server Management Studio) will get the job done, but has its own issues. After doing the rename, to see the renamed SP listed, one must right-click on "Stored Procedures" and choose "refresh". Even after refresh, certain internal caches are in an incorrect state; for example the Editor is unaware of the change (red underline when new name is in a query) and also I think I've seen an error message where none was warranted. To fix that, it is necessary to shut down and restart SSMS.
    – IAM_AL_X
    Commented Mar 3, 2020 at 18:42
-1

sp_rename only change the name of user created objects in database. Renaming a stored procedure 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 you do not rename this object type. Instead, drop and re-create the stored procedure with its new name. even you should not use it use to rename to change to change the name of view, function,or trigger. If you will try to use, it will rename it, but you will also get some warning message something like this:

Changing any part of an object name can break scripts and stored procedures.

For more information you can visit.

http://onlyforcoder.blogspot.in/2017/11/sprename-where-to-use-where-not-to-use.html http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

Improve this answer
1
  • This should get downvoted. It is a useless repetition of information previously presented by other people. Same goes for the "blogspot" link. The "sqlauthority" link is completely off topic.
    – IAM_AL_X
    Commented Mar 3, 2020 at 18:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.