sp_iqrename procedure

Function

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 ] 

Permissions

Must be the owner of the table or have DBA authority or alter permission on the object. Requires exclusive access to the object.

Usage

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. Note that 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.

Table 7-52: 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.

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

See also

ALTER TABLE statement RENAME clause and ALTER INDEX statement RENAME clause in Reference: Statements and Options