sp_rename

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

Syntax

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

objname – is the original name of the user-created object (table, view, column, partition, 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.
“partition” – specifies that the object you are renaming is a partition when the table-partition name conflicts with a column or index name.

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"
```
Example 6 – Renames the table index my_tab.ind1.i_part1 to i_part1_rename:
```
sp_rename "my_tab.ind1.i_part1", i_part1_rename
```
Example 7 – Renames the index partition my_tab.ind1.ind1_928003306 to ind1_928003306_rename using “partition” to avoid conflicts between the table-partition name and index name:
```
sp_rename "my_tab.ind1.ind1_928003306", ind1_928003306_rename, "partition"
```

There are additional considerations when using sp_rename:

sp_rename changes the name of a user-created object or datatype. You can change only the name of an object or datatype in the database in which you issue sp_rename.
When you are renaming a column or index, do not specify the table name in newname. See Examples 2, 3, and 5.
If a column and an index have the same name, use the [,“index” | “column”] argument, which specifies whether to rename the index or the column. In the following sample, assume that both an index and a column named idx exist:
```
sp_rename "t.idx", new_idx, "column"
-------------
Column name has been changed. (Return status = 0)
sp_rename "t.idx", new_idx, "index"
-------------
Index name has been changed. (Return status = 0)
```
If you change the name of a an object or column name referenced by a view, you see a warning message, such as:
```
Changing an object or column name could break
existing stored procedures, cached statements or
other compiled objects.
```
sp_engine can run in sessions using chained transaction mode if there are no open transactions.
You cannot change the names of system objects and system datatypes.

You must be the object owner to execute sp_rename. Permission checks do not differ based on the granular permissions settings.

Use the setuser command to assume another database user’s identity to rename objects owned by other users.

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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