sp_rename (Transact-SQL)sp_rename (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

在当前数据库中更改用户创建对象的名称。Changes the name of a user-created object in the current database. 此对象可以是表、 索引、 列、 别名数据类型,或MicrosoftMicrosoft.NET Framework.NET Framework公共语言运行时 (CLR) 用户定义类型。This object can be a table, index, column, alias data type, or MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) user-defined type.

注意

更改对象名的任一部分都可能破坏脚本和存储过程。Changing any part of an object name can break scripts and stored procedures. 我们建议您不要使用此语句来重命名存储过程、触发器、用户定义函数或视图;而是删除该对象,然后使用新名称重新创建该对象。We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

[ @objname = ] 'object_name'[ @objname = ] 'object_name'
用户对象或数据类型的当前限定或非限定名称。Is the current qualified or nonqualified name of the user object or data type. 如果要重命名的对象是表中的列object_name必须在窗体table.columnschema.table.column 中使用If the object to be renamed is a column in a table, object_name must be in the form table.column or schema.table.column. 如果要重命名的对象的索引object_name必须在窗体table.indexschema.table.indexIf the object to be renamed is an index, object_name must be in the form table.index or schema.table.index. 如果要重命名的对象是一个约束object_name必须在窗体schema.constraintIf the object to be renamed is a constraint, object_name must be in the form schema.constraint.

只有在指定了限定对象时才必须使用引号。Quotation marks are only necessary if a qualified object is specified. 如果提供的是完全限定名称(包括数据库名称),则数据库名称必须是当前数据库的名称。If a fully qualified name, including a database name, is provided, the database name must be the name of the current database. object_namenvarchar(776) ,无默认值。object_name is nvarchar(776), with no default.

[ @newname = ] 'new_name'[ @newname = ] 'new_name'
指定对象的新名称。Is the new name for the specified object. new_name必须是名称的一部分并且必须遵循有关标识符的规则。new_name must be a one-part name and must follow the rules for identifiers. newnamesysname,无默认值。newname is sysname, with no default.

备注

触发器名称不能以 # 或 ## 开头。Trigger names cannot start with # or ##.

[ @objtype = ] 'object_type'[ @objtype = ] 'object_type'
要重命名的对象的类型。Is the type of object being renamed. object_typevarchar(13) ,默认值为 NULL,并且可以是下列值之一。object_type is varchar(13), with a default of NULL, and can be one of these values.

Value 描述Description
COLUMNCOLUMN 要重命名的列。A column to be renamed.
DATABASEDATABASE 用户定义数据库。A user-defined database. 重命名数据库时需要此对象类型。This object type is required when renaming a database.
INDEXINDEX 用户定义索引。A user-defined index. 重命名带统计信息的索引时,也会自动重命名统计信息。Renaming an index with statistics, also automatically renames the statistics.
OBJECTOBJECT 一种类型的项中跟踪sys.objectsAn item of a type tracked in sys.objects. 例如,OBJECT 可用于重命名包含约束(CHECK、FOREIGN KEY、PRIMARY/UNIQUE KEY)、用户表和规则的对象。For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, and rules.
STATISTICSSTATISTICS 适用范围:SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

由用户显式创建的统计信息或使用索引隐式创建的统计信息。Statistics created explicitly by a user or created implicitly with an index. 重命名索引的统计信息时,也会自动重命名索引。Renaming the statistics of an index automatically renames the index as well.
USERDATATYPEUSERDATATYPE 一个CLR 用户定义类型通过执行添加CREATE TYPEsp_addtypeA CLR User-defined Types added by executing CREATE TYPE or sp_addtype.

返回代码值Return Code Values

0(成功)或非零数字(失败)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.

每当重命名 PRIMARY KEY 或 UNIQUE 约束时,sp_rename 都会自动重命名关联的索引。sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. 如果重命名的索引与 PRIMARY KEY 约束关联,则 sp_rename 也会自动重命名该 PRIMARY KEY 约束。If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

sp_rename 可用于重命名主要和次要的 XML 索引。sp_rename can be used to rename primary and secondary XML indexes.

重命名存储的过程、 函数、 视图或触发器将不更改相应的对象的名称中的定义列sys.sql_modules目录视图中或使用获取OBJECT_定义内置函数。Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. 因此,我们建议不要使用 sp_rename 重命名这些对象类型。Therefore, we recommend that sp_rename not be used to rename these object types. 而是删除对象,然后使用新名称重新创建该对象。Instead, drop and re-create the object with its new name.

重命名表或列等对象将不会自动重命名对该对象的引用。Renaming an object such as a table or column will not automatically rename references to that object. 您必须手动修改引用已重命名对象的任何对象。You must modify any objects that reference the renamed object manually. 例如,如果您重命名表列,并且触发器中引用了该列,则必须修改触发器以反映新的列名。For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. 请使用 sys.sql_expression_dependencies 在重命名对象之前列出对象的依赖关系。Use sys.sql_expression_dependencies to list dependencies on the object before renaming it.

权限Permissions

若要重命名对象、列和索引,则需要对该对象具有 ALTER 权限。To rename objects, columns, and indexes, requires ALTER permission on the object. 若要重命名用户类型,则需要对该类型具有 CONTROL 权限。To rename user types, requires CONTROL permission on the type. 若要重命名数据库,则需要具备 sysadmin 或 dbcreator 固定服务器角色的成员身份To rename a database, requires membership in the sysadmin or dbcreator fixed server roles

示例Examples

A.A. 重命名表Renaming a table

下面的示例将 SalesTerritory 架构中的 SalesTerr 表重命名为 SalesThe following example renames the SalesTerritory table to SalesTerr in the Sales schema.

USE AdventureWorks2012;  
GO  
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';  
GO  

B.B. 重命名列Renaming a column

以下示例将重命名TerritoryID中的列SalesTerritory表向TerrIDThe following example renames the TerritoryID column in the SalesTerritory table to TerrID.

USE AdventureWorks2012;  
GO  
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
GO  

C.C. 重命名索引Renaming an index

下面的示例将 IX_ProductVendor_VendorID 索引重命名为 IX_VendorIDThe following example renames the IX_ProductVendor_VendorID index to IX_VendorID.

USE AdventureWorks2012;  
GO  
EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';  
GO  

D.D. 重命名别名数据类型Renaming an alias data type

下面的示例将 Phone 别名数据类型重命名为 TelephoneThe following example renames the Phone alias data type to Telephone.

USE AdventureWorks2012;  
GO  
EXEC sp_rename N'Phone', N'Telephone', N'USERDATATYPE';  
GO  

E.E. 重命名约束Renaming constraints

下面的示例重命名 PRIMARY KEY 约束、CHECK 约束和 FOREIGN KEY 约束。The following examples rename a PRIMARY KEY constraint, a CHECK constraint and a FOREIGN KEY constraint. 重命名约束时,必须指定约束所属的架构。When renaming a constraint, the schema to which the constraint belongs must be specified.

USE AdventureWorks2012;   
GO  
-- Return the current Primary Key, Foreign Key and Check constraints for the Employee table.  
SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc  
FROM sys.objects  
WHERE parent_object_id = (OBJECT_ID('HumanResources.Employee'))   
AND type IN ('C','F', 'PK');   
GO  
  
-- Rename the primary key constraint.  
sp_rename 'HumanResources.PK_Employee_BusinessEntityID', 'PK_EmployeeID';  
GO  
  
-- Rename a check constraint.  
sp_rename 'HumanResources.CK_Employee_BirthDate', 'CK_BirthDate';  
GO  
  
-- Rename a foreign key constraint.  
sp_rename 'HumanResources.FK_Employee_Person_BusinessEntityID', 'FK_EmployeeID';  
  
-- Return the current Primary Key, Foreign Key and Check constraints for the Employee table.  
SELECT name, SCHEMA_NAME(schema_id) AS schema_name, type_desc  
FROM sys.objects  
WHERE parent_object_id = (OBJECT_ID('HumanResources.Employee'))   
AND type IN ('C','F', 'PK');  
  
  
name                                  schema_name        type_desc  
------------------------------------- ------------------ ----------------------  
FK_Employee_Person_BusinessEntityID   HumanResources     FOREIGN_KEY_CONSTRAINT  
PK_Employee_BusinessEntityID          HumanResources     PRIMARY_KEY_CONSTRAINT  
CK_Employee_BirthDate                 HumanResources     CHECK_CONSTRAINT  
CK_Employee_MaritalStatus             HumanResources     CHECK_CONSTRAINT  
CK_Employee_HireDate                  HumanResources     CHECK_CONSTRAINT  
CK_Employee_Gender                    HumanResources     CHECK_CONSTRAINT  
CK_Employee_VacationHours             HumanResources     CHECK_CONSTRAINT  
CK_Employee_SickLeaveHours            HumanResources     CHECK_CONSTRAINT  
  
(7 row(s) affected)  
  
name                                  schema_name        type_desc  
------------------------------------- ------------------ ----------------------  
FK_Employee_ID                        HumanResources     FOREIGN_KEY_CONSTRAINT  
PK_Employee_ID                        HumanResources     PRIMARY_KEY_CONSTRAINT  
CK_BirthDate                          HumanResources     CHECK_CONSTRAINT  
CK_Employee_MaritalStatus             HumanResources     CHECK_CONSTRAINT  
CK_Employee_HireDate                  HumanResources     CHECK_CONSTRAINT  
CK_Employee_Gender                    HumanResources     CHECK_CONSTRAINT  
CK_Employee_VacationHours             HumanResources     CHECK_CONSTRAINT  
CK_Employee_SickLeaveHours            HumanResources     CHECK_CONSTRAINT  
  
(7 row(s) affected)  
  

F.F. 重命名统计信息Renaming statistics

下面的示例创建一个名为 contactMail1 的统计信息对象,然后将重命名统计信息为 NewContact 使用 sp_rename。The following example creates a statistics object named contactMail1 and then renames the statistic to NewContact by using sp_rename. 重命名统计信息时,必须采用格式 schema.table.statistics_name 指定该对象。When renaming statistics, the object must be specified in the format schema.table.statistics_name.

CREATE STATISTICS ContactMail1  
    ON Person.Person (BusinessEntityID, EmailPromotion)  
    WITH SAMPLE 5 PERCENT;  
  
sp_rename 'Person.Person.ContactMail1', 'NewContact','Statistics';  
  

请参阅See Also

sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
数据库引擎存储过程(Transact SQL)Database Engine Stored Procedures (Transact-SQL)