sp_rename System Stored Procedure

Changes the name of a user-created object in the current database.

Syntax

sp_rename objname, newname [, COLUMN | INDEX ]

where

objname

Is the original name of the user object (table, view, column, stored procedure, trigger, default, or rule) or datatype. If the object to be renamed is a column in a table, objname must be in the form 'table.column'. If the object to be renamed is an index, objname must be in the form 'table.index'. The table name is not included in newname. You can rename an object in the current database only, and only if you own it. This rule holds for all users, including the system administrator and database owner. Users can change the names only of those objects they own. The database owner or system administrator can change the name of any user's objects by using SETUSER. For details, see the SETUSER statement.

newname

Is the new name of the object or datatype. Names of objects and datatypes must follow the rules for identifiers. When the table name is required in objname, it is excluded from newname.

COLUMN | INDEX

Specifies whether the object being renamed is a column or an index. Specifying this parameter resolves any possible conflict that can occur when column names and index names are identical.

Remarks

You can change the name of an object or datatype in the current database only. The names of most system datatypes 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.

Examples

A.    Rename a Table

This example renames the titles table books.

sp_rename titles, books

B.    Rename a Column

This example renames the title column in the books table name.

sp_rename 'books.title', name, COLUMN

Permission

Execute permission defaults to the object owner.

Tables Used

syscolumns, sysindexes, sysobjects, systypes

See Also