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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) 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 common language runtime (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.column或是schema.table.columnIf 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.index或是schema.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 2017 以及 Azure 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 A CLR 使用者定義型別來加入CREATE TYPE或是sp_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.

重新命名預存程序、 函數、 檢視或觸發程序不會變更對應的物件名稱中的 definition 資料行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.

PermissionsPermissions

若要重新命名物件、資料行和索引,需要物件的 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)
Database Engine 預存程序(Transact SQL)Database Engine Stored Procedures (Transact-SQL)