sp_rename (version 6.5)

Changes the name of a user-created object (for example, table, column, user datatype) in the current database.

For additional syntax information for sp_rename, see the Microsoft SQL Server Transact-SQL Reference.

Syntax

sp_rename oldname, newname [, COLUMN | INDEX | OBJECT | USERDATATYPE]

where

oldname

Is the name of the object to rename. The database owner or system administrator can change the name of user-created objects without using SETUSER. A fully qualified object can be specified.

newname

Is the new name for the specified object. The database owner or system administrator can change the name of user-created objects without using SETUSER. A fully qualified object name can be specified.

COLUMN | INDEX | OBJECT | USERDATATYPE]

Specifies whether the object being renamed is a column, index, object, or user-defined datatype.

Value	Definition
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 datatype added by executing sp_addtype.

Remarks

This procedure 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, consider dropping and re-creating all dependent stored procedures and views. Some patterns of renaming may confuse the behavior of the dependent objects.

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.

Caution Using sp_rename to rename textual objects may put the new object name in the name column of the sysobjects system table and leave the previous name for the object in the text column of the syscomments system table.

Example

In this example, the database owner renames a Primary Key constraint owned by another user.

IF user_id()=1
    EXECUTE sp_rename 'dab32.usr_sally.cns_tab44_pky', 'pky44', 'object'
  

Permission

The system administrator, the database owner, or the object owner can execute this system stored procedure.