Changes the name of a user-created object or user-defined datatype in the current database.
sp_rename objname, newname [,“index” | “column”]
is the original name of the user-created object (table, view, column, stored procedure, index, trigger, default, rule, check constraint, referential constraint, or user-defined datatype). If the object to be renamed is a column in a table, objname must be in the form “table.column”. If the object is an index, objname must be in the form “table.indexname”.
is the new name of the object or datatype. The name must conform to the rules for identifiers and must be unique to the current database.
specifies that the object you are renaming is an index, not a column. This argument allows you to rename an index that has the same name as a column, without dropping and re-creating the index.
specifies that the object you are renaming is a column, not an index. This argument is part of the same option as the index argument.
Renames the titles table to books:
sp_rename titles, books
Renames the title column in the books table to bookname:
sp_rename "books.title", bookname
Renames the titleind index in the books table to titleindex:
sp_rename "books.titleind", titleindex
Renames the user-defined datatype tid to bookid:
sp_rename tid, bookid
renames the title_id index in the titles table to isbn.
sp_rename "titles.title_id", isbn, "index"
sp_rename changes the name of a user-created object or datatype. You can change only the name of an object or datatype in the database in which you issue sp_rename.
When you are renaming a column or index, do not specify the table name in newname. See Examples 2, 3, and 5.
If a column and an index have the same name, use the [,“index” | “column”] argument, which specifies whether to rename the index or the column. In the following sample, assume that both an index and a column named idx exist:
sp_rename "t.idx", new_idx, "column" -------------
Column name has been changed. (Return status = 0)
sp_rename "t.idx", new_idx, "index" -------------
Index name has been changed. (Return status = 0)
You can change the name of a an object referenced by a view. For example, if a view references the new_sales table and you rename new_sales to old_sales, the view will reference old_sales.
You cannot change the names of system objects and system datatypes.
WARNING! Procedures, triggers, and views that depend on an object whose name has been changed work until they are dropped and re-created. Also, the old object name appears in query results until the user changes and re-creates the procedure, trigger, or view. Change the definitions of any dependent objects when you execute sp_rename. Find dependent objects with sp_depends.
Only the Database Owner or a System Administrator can use the setuser command to assume another database user’s identity to rename objects owned by other users. All users can execute sp_rename to rename their own objects.
System procedures sp_depends, sp_rename
