Renames user-created tables, columns, indexes, constraints (unique, primary key, foreign key, and check), stored procedures, and functions.
sp_iqrename object-name, new-name [, object-type”]
Must be the owner of the table or have DBA authority or alter permission on the object. Requires exclusive access to the object.
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.
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 procedure or function |
object-type not specified |
The object being renamed is a table or view |
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.
ALTER TABLE statement RENAME clause in Chapter 6, “SQL Statements”
ALTER INDEX statement RENAME clause in Chapter 6, “SQL Statements”
The sp_iqrename stored procedure renames user-created tables, views, columns, indexes, constraints (unique, primary key, foreign key, and check), stored procedures, 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 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.
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
Renames the procedure getnamep to gettitlep:
sp_iqrename getnamep, gettitlep, procedure
