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.
Following are the permissions required for different type of objects
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.
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.
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.
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.
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.