Syntax:
sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
Arguments:
[ @objname = ] 'object_name'
Is the current qualified or nonqualified name of the user object or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column or schema.table.column. If the object to be renamed is an index, object_name must be in the form table.index or schema.table.index.
Quotation marks are only necessary if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. object_name is nvarchar(776), with no default.
[ @newname = ] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[ @objtype = ] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
Return Code Values
0 (success) or a nonzero number (failure)
Remarks
You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.
sp_rename can be used to rename primary and secondary XML indexes.
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.
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.
Permissions
To rename objects, columns, and indexes, requires ALTER permission on the object. To rename user types, requires CONTROL permission on the type. To rename a database, requires membership in the sysadmin or dbcreator fixed server roles
Examples
A. Renaming a table
The following example renames the SalesTerritory table to SalesTerr in the Sales schema.
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';
GO
B. Renaming a column
The following example renames the TerritoryID column in the SalesTerritory table to TerrID.
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
C. Renaming an index
The following example renames the IX_ProductVendor_VendorID index to IX_VendorID.
USE AdventureWorks;
GO
EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';
GO
D. Renaming an alias data type
The following example renames the Phone alias data type to Telephone.
USE AdventureWorks;
GO
EXEC sp_rename N'Phone', N'Telephone', N'USERDATATYPE';
GO
sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
Arguments:
[ @objname = ] 'object_name'
Is the current qualified or nonqualified name of the user object or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column or schema.table.column. If the object to be renamed is an index, object_name must be in the form table.index or schema.table.index.
Quotation marks are only necessary if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. object_name is nvarchar(776), with no default.
[ @newname = ] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[ @objtype = ] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
| Value | Description |
| COLUMN | A column to be renamed. |
| DATABASE | A user-defined database. This object type is required when renaming a database. |
| INDEX | A user-defined index. |
| OBJECT | An item of a type tracked in sys.objects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, and rules. |
| USERDATATYPE | An alias data type or CLR User-defined Types added by executing CREATE TYPE or sp_addtype. |
Return Code Values
0 (success) or a nonzero number (failure)
Remarks
You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.
sp_rename can be used to rename primary and secondary XML indexes.
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.
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.
Permissions
To rename objects, columns, and indexes, requires ALTER permission on the object. To rename user types, requires CONTROL permission on the type. To rename a database, requires membership in the sysadmin or dbcreator fixed server roles
Examples
A. Renaming a table
The following example renames the SalesTerritory table to SalesTerr in the Sales schema.
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';
GO
B. Renaming a column
The following example renames the TerritoryID column in the SalesTerritory table to TerrID.
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
C. Renaming an index
The following example renames the IX_ProductVendor_VendorID index to IX_VendorID.
USE AdventureWorks;
GO
EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';
GO
D. Renaming an alias data type
The following example renames the Phone alias data type to Telephone.
USE AdventureWorks;
GO
EXEC sp_rename N'Phone', N'Telephone', N'USERDATATYPE';
GO
