Home

	Expand Search

Transact-SQL User's Guide

Chapter 7 Creating Databases and Tables

Altering existing tables

Renaming tables and other objects

Renaming tables and other objects

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 sp_renamedb in the Reference Manual.

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" 

When you rename objects, you must also change the text of any dependent procedure, trigger, or view to reflect the new object name. The original object name continues to appear in query results until you change the name of, and compile the procedure, trigger, or view. The safest course is to change the definitions of any dependent objects when you execute sp_rename. You can get a list of dependent objects using sp_depends.

You can use the defncopy utility program to copy the definitions of procedures, triggers, rules, defaults, and views into an operating system file. Edit this file to correct the object names, then use defncopy to copy the definition back into Adaptive Server. For more information on defncopy, refer to The Utility Guide.