Export (0) Print
Expand All
Expand Minimize
25 out of 36 rated this helpful - Rate this topic

sp_rename

SQL Server 2000

Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.

Syntax

sp_rename [ @objname = ] 'object_name' ,
    
[ @newname = ] 'new_name'
    [ , [ @objtype = ] 'object_type' ]

Arguments

[@objname =] 'object_name'

Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.

[@newname =] 'new_name'

Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.

[@objtype =] 'object_type'

Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.

Value Description
COLUMN A column to be renamed.
DATABASE A user-defined database. This option is required when renaming a database.
INDEX A user-defined index.
OBJECT An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.
USERDATATYPE A user-defined data type added by executing sp_addtype.

Return Code Values

0 (success) or a nonzero number (failure)

Remarks

You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.

When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.

Important  After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.

Renaming a stored procedure, view or trigger will not change the name of the corresponding object name in the syscomments table. This may result in problems generating a script for the object as the old name will be inserted from the syscomments table into the CREATE statement. For best results, do not rename these object types. Instead, drop and re-create the object by its new name.

Permissions

Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with 'database' as the object_type.

Examples
A. Rename a table

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'
B. Rename a column

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

See Also

ALTER TABLE

CREATE DEFAULT

CREATE PROCEDURE

CREATE RULE

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

Data Types

SETUSER

sp_addtype

sp_depends

sp_renamedb

System Stored Procedures

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.