:::: MENU ::::

Considerations while using SP_Rename to change name of object

  • Apr 11 / 2017
  • 0
System Stored Procedures, T-SQL Tips and Tricks

Considerations while using SP_Rename to change name of object

Before going to concerned point that i want to share through this post i.e. considerations while using sp_rename,  i would like to summarize the scope and features of Sp_Rename system stored procedure.

Scope and Features of Sp_Rename

Sp_Rename is used the change the name of any user object in a SQL Server database or even the database itself. It accepts three parameters for current name, new name and object type. Generally object type parameter (@objtype) can be skipped unless there is a name conflict for concerned object.

Required Permissions

Following are the permissions required for different type of objects

Permissions for using sp_rename in SQL Server T-SQL

Issues while using sp_rename

There are some associated issues while using sp_rename. These should be known and taken care of

Using schema name in new name parameter

You can use schema name along with object name in current name of object but DO NOT use it in new name provided to SP as parameter. It would also take the schema name as object name. Consider following example for this scenario.

 

-- Use any test DB
Use Test
GO
-- Create table
Create table TableName_Old (id int identity(1,1), FName varchar(50))
GO
-- Verify Table name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_Old'

-- Rename table
exec sp_rename 'dbo.TableName_Old', 'dbo.TableName_New'
GO

And when we tried to verify with table with new name the result is empty.

-- Verify table with new name
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like 'TableName_new'

Let us run a generic search to include more tables.

-- Verify table with generic search
select schema_name(schema_id) as SchemaName, name
from sys.objects
where type = 'U' and name like '%TableName%'

Now we have following result that shows that schema dbo is also prefixed in name of table.
Result of sp_rename T-SQL

So we have to execute another sp_rename command to set the required name of this table.


-- Rename and remove the schema prefix
exec sp_rename 'dbo.[dbo.TableName_New]', 'TableName_New'
GO

This fixed out the issue and we have table name as TableName_New.

SP_rename for name correction T-SQL

Effect on Associated Indexes

Associated index will be automatically renamed whenever a PRIMARY KEY or UNIQUE constraint is renamed.
In same way if a renamed index is associated to a PRIMARY KEY constraint,
the PRIMARY KEY constraint will be automatically renamed.

References to Object

Renaming an object will not update its references to that object. This task should be done manually to avoid code failure after rename.

Code 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.

Conclusion

Sp_rename should be avoided if you have option to drop and recreate an object with a different name. Analysis of code base is imperative to make sure that code will not break after rename is performed.

Consult us to explore the Databases. Contact us