sp_rename

Description

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

Syntax

sp_rename objname, newname [,“index” | “column”]

Parameters

objname

is the original name of the user-created object (table, view, column, stored procedure, index, trigger, default, rule, check constraint, referential constraint, or user-defined datatype). If the object to be renamed is a column in a table, objname must be in the form “table.column”. If the object is an index, objname must be in the form “table.indexname”.

newname

is the new name of the object or datatype. The name must conform to the rules for identifiers and must be unique to the current database.

index

specifies that the object you are renaming is an index, not a column. This argument allows you to rename an index that has the same name as a column, without dropping and re-creating the index.

column

specifies that the object you are renaming is a column, not an index. This argument is part of the same option as the index argument.

Examples

Example 1

Renames the titles table to books:

sp_rename titles, books

Example 2

Renames the title column in the books table to bookname:

sp_rename "books.title", bookname

Example 3

Renames the titleind index in the books table to titleindex:

sp_rename "books.titleind", titleindex

Example 4

Renames the user-defined datatype tid to bookid:

sp_rename tid, bookid

Example 5

Renames the title_id index in the titles table to isbn:

sp_rename "titles.title_id", isbn, "index"

Usage

Permissions

Only the Database Owner or a System Administrator can use the setuser command to assume another database user’s identity to rename objects owned by other users. All users can execute sp_rename to rename their own objects.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

System procedures sp_depends, sp_rename