Sql-server – Are there any reasons to avoid using sp_rename for renaming objects

sql serversql server 2014sql-server-2012t-sql

In the documentation it is clearly said:

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.

What about renaming:

  • primary keys
  • unique keys
  • foreign keys
  • default constraints
  • indexes

I am going to rename all of the above objects in order to match our internal naming conventions but I want to write an automatic script for doing this (as there are thousands of constraints).

The only issue I can think off is that if index is used with a hint in statement it will be broken.

Could you tell any other reasons not to use the sp_rename for such purpose?

Best Answer

  • Update

sp_rename upates the name in sys.objects but it does not update the definition or objects referencing it.

  • Object definition

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. (MSDN)

When you run this query:

Create Procedure Test
as
begin
    set nocount on
end
Go
Exec sp_rename 'Test', 'Tests'
GO
Select * From sys.sql_modules
sp_helptext 'Tests'

The definition column in sql_modules still contains Test:

object_id   definition ...
599673184   Create Procedure Test as begin set nocount on Select id From T1 end
  • Rename a Table (or an object)

When an object is rename, any object using it and referencing the old name will fail.

Here are a View and a Table beining renamed:

Create Table T1(id int)
Go
Create View V1 AS Select id From dbo.T1
Go  
Exec sp_rename 'T1', 'T2'

Again if you look at the definition, both the procedure and the view reference T1 (which does not exist anymore):

object_id   definition
759673754   Create Procedure Test as begin set nocount on Select id From T1 end
791673868   Create View V1 AS Select id From dbo.T1

And both will fail with this error when you Select from it or Execute it:

Invalid object name 'dbo.T1'.
Could not use view or function 'V1' because of binding errors.

You get the same error if you refresh the view:

sp_refreshview 'v1'

All objects with references to the old name will break.

  • Dependencies

Before renaming an object (table, procedure, function, ...), you can look at its dependencies using sys.sql_expression_dependencies (MSDN) (deprecated: sys.sql_dependencies MSDN, sys.sysdepends (MSDN)

Related Question