Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.
sp_rename [@objname =] 'object_name',
[@newname =] 'new_name'
[, [@objtype =] 'object_type']
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. |
0 (success) or a nonzero number (failure)
None
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.
Members of the sysadmin fixed server role, the db_owner fixed database role, or the owner of the object can execute sp_rename.
This example renames the customers table to custs.
EXEC sp_rename 'customers', 'custs'
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
ALTER TABLE | Data Types |
CREATE DEFAULT | SETUSER |
CREATE PROCEDURE | sp_addtype |
CREATE RULE | sp_depends |
CREATE TABLE | sp_renamedb |
CREATE TRIGGER | System Stored Procedures |
CREATE VIEW |