重命名存储过程Rename a Stored Procedure

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中重命名存储过程。This topic describes how to rename a stored procedure in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 过程名称必须符合 标识符规则。Procedure names must comply with the rules for identifiers.

  • 重命名存储过程会保留 object_id 以及专门分配给该过程的所有权限。Renaming a stored procedure retains the object_id and all the permissions that are specifically assigned to the procedure. 删除并重新创建对象将创建一个新的 object_id,并删除专门分配给该过程的所有权限。Dropping and recreating the object creates a new object_id and removes any permissions specifically assign to the procedure.

  • 重命名存储过程将不会更改 sys.sql_modules 目录视图的定义列中相应对象名的名称。Renaming a stored procedure does not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. 要执行该操作,必须删除存储过程,然后使用新名称重新创建该存储过程。To do that, you must drop and re-create the stored procedure with its new name.

  • 在未将对象更新为反映已对过程所做的更改时,更改过程的名称或定义可能导致依赖对象失败。Changing the name or definition of a procedure can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the procedure. 有关详细信息,请参阅 查看存储过程的依赖关系For more information, see View the Dependencies of a Stored Procedure.

SecuritySecurity

权限Permissions

CREATE PROCEDURECREATE PROCEDURE
要求数据库中的 CREATE PROCEDURE 权限以及对要在其中创建过程的架构的 ALTER 权限,或者要求 db_ddladmin 固定数据库角色中的成员身份。Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

ALTER PROCEDUREALTER PROCEDURE
要求对过程具有 ALTER 权限,或者要求 db_ddladmin 固定数据库角色中的成员身份。Requires ALTER permission on the procedure or requires membership in the db_ddladmin fixed database role.

使用 SQL Server Management StudioUsing SQL Server Management Studio

重命名存储过程To rename a stored procedure

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.
  2. 展开 “数据库”、过程所属的数据库以及 “可编程性”Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
  3. 确定存储过程的依赖关系Determine the dependencies of the stored procedure.
  4. 展开“存储过程”,右键单击要重命名的过程,再单击“重命名”。Expand Stored Procedures, right-click the procedure to rename, and then click Rename.
  5. 修改过程名称。Modify the procedure name.
  6. 修改在任何相关对象或脚本中引用的过程名称。Modify the procedure name referenced in any dependent objects or scripts.

使用 Transact-SQLUsing Transact-SQL

重命名存储过程To rename a stored procedure

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.
  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.
  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何通过删除过程并使用新名称重新创建该过程来重命名过程。This example shows how to rename a procedure by dropping the procedure and re-creating the procedure with a new name. 第一个示例将创建 'HumanResources.uspGetAllEmployeesTest存储过程。The first example creates the stored procedure 'HumanResources.uspGetAllEmployeesTest. 第二个示例将存储过程重命名为 HumanResources.uspEveryEmployeeTestThe second example renames the stored procedure to HumanResources.uspEveryEmployeeTest.
--Create the stored procedure.  
USE AdventureWorks2012;  
GO  

CREATE PROCEDURE HumanResources.uspGetAllEmployeesTest  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory;  
GO  
  
--Rename the stored procedure.  
EXEC sp_rename 'HumanResources.uspGetAllEmployeesTest', 'uspEveryEmployeeTest'; 

另请参阅See Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL) CREATE PROCEDURE (Transact-SQL)
创建存储过程 Create a Stored Procedure
修改存储过程 Modify a Stored Procedure
删除存储过程 Delete a Stored Procedure
查看存储过程的定义 View the Definition of a Stored Procedure
查看存储过程的依赖关系View the Dependencies of a Stored Procedure