sp_iqrename Procedure

Syntax

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

Parameters

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 privileges 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.
Valid values are:
- 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.

Privileges

You must have EXECUTE privilege on the system procedure. Different privileges are needed to rename specific object types as follows:

ALTER ANY OBJECT system privilege – You can rename any object.
ALTER ANY TABLE system privilege – You can rename any table, column or constraint.
ALTER ANY INDEX system privilege – You can rename any index, but not tables or columns.
REFERENCES privilege on a table – You can rename indexes of that table only.
ALTER privilege on the table – You can rename that table, its columns, and constraints only.
You own the table – You can rename any objects you own.

You must also have exclusive access to the object is required to run this procedure.

Remarks

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.

Example

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