sp_rename (T-SQL)

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)

Result Sets

None

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 fixed database role, or the owner of the object can execute sp_rename.

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'

See Also