查看存储过程的定义View the Definition of 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 Management StudioSQL Server Management Studio 中使用“对象资源管理器”菜单选项或在查询编辑器中使用 Transact-SQLTransact-SQL来查看存储过程的定义。You can view the definition of a stored procedure in SQL Server Management StudioSQL Server Management Studio using Object Explorer menu options or in the Query Editor using Transact-SQLTransact-SQL. 本主题介绍如何在对象资源管理器中查看过程的定义,以及如何在查询编辑器中使用系统存储过程、系统函数和对象目录视图来查看过程的定义。This topic describes how to view the definition of procedure in Object Explorer and by using a system stored procedure, system function, and object catalog view in the Query Editor.

开始之前Before You Begin

安全性Security

PermissionsPermissions

系统存储过程: sp_helptextSystem Stored Procedure: sp_helptext
要求 公共 角色具有成员身份。Requires membership in the public role. 系统对象定义对所有用户可见。System object definitions are publicly visible. 用户对象的定义对于对象所有者或具有下列任一权限的被授权者可见:ALTER、CONTROL、TAKE OWNERSHIP 或 VIEW DEFINITION。The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.

系统函数: OBJECT_DEFINITIONSystem Function: OBJECT_DEFINITION
系统对象定义对所有用户可见。System object definitions are publicly visible. 用户对象的定义对于对象所有者或具有下列任一权限的被授权者可见:ALTER、CONTROL、TAKE OWNERSHIP 或 VIEW DEFINITION。The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. db_ownerdb_ddladmindb_securityadmin 固定数据库角色的成员隐式具有这些权限。These permissions are implicitly held by members of the db_owner, db_ddladmin, and db_securityadmin fixed database roles.

sys.sql_modules 目录视图: sys.sql_modulesObject Catalog View: sys.sql_modules
目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

如何查看存储过程的定义How to View the Definition of a Stored Procedure

您可以使用以下项之一:You can use one of the following:

使用 SQL Server Management StudioUsing SQL Server Management Studio

在对象资源管理器中查看过程的定义To view the definition a procedure in Object Explorer

  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. 展开 “存储过程”,右键单击该过程,再单击 “编写存储过程脚本为”,然后单击下列选项之一: “CREATE 到”“ALTER 到”“DROP 和 CREATE 到”Expand Stored Procedures, right-click the procedure and then click Script Stored Procedure as, and then click one of the following: Create To, Alter To, or Drop and Create To.

  4. 选择 “新建查询编辑器窗口”Select New Query Editor Window. 这将显示过程定义。This will display the procedure definition.

使用 Transact-SQLUsing Transact-SQL

在查询编辑器中查看过程的定义To view the definition of a procedure in Query Editor

系统存储过程: sp_helptextSystem Stored Procedure: sp_helptext

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of the 数据库引擎Database Engine.

  2. 在工具栏上,单击 “新建查询”On the toolbar, click New Query.

  3. 在查询窗口中,输入以下使用 sp_helptext 系统存储过程的语句。In the query window, enter the following statement that uses the sp_helptext system stored procedure. 更改数据库名称和存储过程名称以引用所需的数据库和存储过程。Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    EXEC sp_helptext N'AdventureWorks2012.dbo.uspLogError';  
    

系统函数: OBJECT_DEFINITIONSystem Function: OBJECT_DEFINITION

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of the 数据库引擎Database Engine.

  2. 在工具栏上,单击 “新建查询”On the toolbar, click New Query.

  3. 在查询窗口中,输入以下使用 OBJECT_DEFINITION 系统函数的语句。In the query window, enter the following statements that use the OBJECT_DEFINITION system function. 更改数据库名称和存储过程名称以引用所需的数据库和存储过程。Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));  
    

sys.sql_modules 目录视图: sys.sql_modulesObject Catalog View: sys.sql_modules

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of the 数据库引擎Database Engine.

  2. 在工具栏上,单击 “新建查询”On the toolbar, click New Query.

  3. 在查询窗口中,输入以下使用 sys.sql_modules 目录视图的语句。In the query window, enter the following statements that use the sys.sql_modules catalog view. 更改数据库名称和存储过程名称以引用所需的数据库和存储过程。Change the database name and stored procedure name to reference the database and stored procedure that you want.

    USE AdventureWorks2012;  
    GO  
    SELECT definition  
    FROM sys.sql_modules  
    WHERE object_id = (OBJECT_ID(N'AdventureWorks2012.dbo.uspLogError'));  
    

另请参阅See Also

创建存储过程 Create a Stored Procedure
修改存储过程 Modify a Stored Procedure
删除存储过程 Delete a Stored Procedure
重命名存储过程 Rename a Stored Procedure
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sp_helptext (Transact-SQL) sp_helptext (Transact-SQL)
OBJECT_ID (Transact-SQL)OBJECT_ID (Transact-SQL)