sp_iqrename Procedure

Renames user-created tables, columns, indexes, constraints (unique, primary key, foreign key, and check), stored procedures, and functions.

Syntax

sp_iqrename object-name, new-name [, object-type ]

Privileges

User must be the owner of the table, or have ALTER permission on the table, or have either the ALTER ANY OBJECT or ALTER ANY <Object_Type> system privilege. Requires exclusive access to the object.

This procedure can rename table, column, index and constraint. Non-privileged users can rename only objects owned by themselves. Users with ALTER permission on a table can rename that table, its columns, and constraints. Users with the ALTER ANY OBJECT system privilege can rename any object. Users with the ALTER ANY TABLE system privilege can rename any table, column or constraint. Users with the ALTER ANY INDEX system privilege can rename any index, but not tables or columns. User with REFERENCES permission on a table can rename only the indexes of that table.

Usage

Parameters
Parameter	Description
object-name	The original name of the user-created object. Optionally, `owner-name` can be specified as part of `object-name` as `owner-name.object-name`, where `owner-name` is the name of the owner of the object being renamed. If `owner-name` is not specified, the user calling sp_iqrename is assumed to be the owner of the object. The object is successfully renamed only if the user calling sp_iqrename has the required permissions to rename the object. If the object to be renamed is a column, index, or constraint, you must specify the name of the table with which the object is associated. For a column, index, or constraint, `object-name` can be of the form `table-name.object-name` or `owner-name.table-name.object-name`.
new-name	The new name of the object. The name must conform to the rules for identifiers and must be unique for the type of object being renamed.
object-type	An optional parameter that specifies the type of the user-created object being renamed, that is, the type of the object `object-name`. The `object-type` parameter can be specified in either upper or lowercase.

Values for the object-type parameter:

sp_iqrename object-type parameter values
`object-type` parameter	Specifies
column	The object being renamed is a column
index	The object being renamed is an index
constraint	The object being renamed is a unique, primary key, check, or referential (foreign key) constraint
procedure	The object being renamed is a function
`object-type` not specified	The object being renamed is a table

Warning! You must change appropriately the definition of any dependent object (procedures, functions, and views) on an object being renamed by sp_iqrename. The sp_iqrename procedure does not automatically update the definitions of dependent objects. You must change these definitions manually.

Description

The sp_iqrename stored procedure renames user-created tables, columns, indexes, constraints (unique, primary key, foreign key, and check), and functions.

If you attempt to rename an object with a name that is not unique for that type of object, sp_iqrename returns the message “Item already exists.”

sp_iqrename does not support renaming a view, a procedure, an event or a data type. The message “Feature not supported.” is returned by sp_iqrename, if you specify event or datatype as the object-type parameter.

You can also rename using the RENAME clause of the ALTER TABLE statement and ALTER INDEX statement.

Examples

Renames the table titles owned by user shweta to books:

sp_iqrename shweta.titles, books

Renames the column id of the table books to isbn:

sp_iqrename shweta.books.id, isbn, column

Renames the index idindex on the table books to isbnindex:

sp_iqrename books.idindex, isbnindex, index

Renames the primary key constraint prim_id on the table books to prim_isbn:

sp_iqrename books.prim_id, prim_isbn, constraint