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 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.
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.
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
ALTER TABLE statement RENAME clause and ALTER INDEX statement RENAME clause in Reference: Statements and Options