Rename a stored procedure in SQL Server

user_44656 0
Viewed 114399

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.

sql-server-2008 sql-server sql
9 Answers

  • 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)

user_97583880

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.

user_6349350
Related