To rename tables and other database objects—columns, constraints, datatypes, views, indexes, rules, defaults, procedures, and triggers—use sp_rename.
You must own an object to rename it. You cannot change the name of system objects or system datatypes. The Database Owner can change the name of any user’s objects. Also, the object whose name you are changing must be in the current database.
To rename the database, use sp_renamedb. See the Reference Manual: Procedures.
The syntax of sp_rename is:
sp_rename objname, newname
For example, to change the name of friends_etc to infotable:
sp_rename friends_etc, infotable
To rename a column, use:
sp_rename "table.column", newcolumnname
You must leave off the table name prefix from the new column name, or the new name will not be accepted.
To change the name of an index, use:
sp_rename "table.index", newindexname
Do not include the table name in the new name.
To change the name of the user datatype tid to t_id, use:
exec sp_rename tid, "t_id"
