sp_rename
Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.
Syntax
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]
Arguments
[@objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. 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 option is required when renaming a database. |
| INDEX | A user-defined index. |
| OBJECT | An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules. |
| USERDATATYPE | A user-defined data type added by executing 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.
When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.
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 is also automatically renamed by sp_rename.
Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.
Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.
Permissions
Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.
Examples
A. Rename a table
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'
B. Rename a column
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'