View the definition of a stored procedure
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
View the definition of a stored procedure
This article 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.
Security
Permissions
System Stored Procedure: sp_helptext
Requires membership in the public role. System object definitions are publicly visible. 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.
System Function: OBJECT_DEFINITION
System object definitions are publicly visible. 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. These permissions are implicitly held by members of the db_owner, db_ddladmin, and db_securityadmin fixed database roles.
Object 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. For more information, see Metadata Visibility Configuration.
Note
The system stored procedure sp_helptext
is not supported in Azure Synapse Analytics. Instead, use sys.sql_modules
object catalog view. Samples are provided later in this article.
How to View the Definition of a Stored Procedure
You can use one of the following:
Using SQL Server Management Studio
To view the definition a procedure in Object Explorer:
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
Expand Stored Procedures, right-click the procedure and then select Script Stored Procedure as, and then select one of the following: Create To, Alter To, or Drop and Create To.
Select New Query Editor Window. This will display the procedure definition.
Using Transact-SQL
To view the definition of a procedure in Query Editor
System Stored Procedure: sp_helptext
In Object Explorer, connect to an instance of the Database Engine.
On the toolbar, select New Query.
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 AdventureWorks2022; GO EXEC sp_helptext N'AdventureWorks2022.dbo.uspLogError';
System Function: OBJECT_DEFINITION
In Object Explorer, connect to an instance of the Database Engine.
On the toolbar, select New Query.
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 AdventureWorks2022; GO SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2022.dbo.uspLogError'));
Object Catalog View: sys.sql_modules
In Object Explorer, connect to an instance of the Database Engine.
On the toolbar, select New Query.
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 AdventureWorks2022; GO SELECT [definition] FROM sys.sql_modules WHERE object_id = (OBJECT_ID(N'dbo.uspLogError'));
See also
Váš názor
https://aka.ms/ContentUserFeedback.
Připravujeme: V průběhu roku 2024 budeme postupně vyřazovat problémy z GitHub coby mechanismus zpětné vazby pro obsah a nahrazovat ho novým systémem zpětné vazby. Další informace naleznete v tématu:Odeslat a zobrazit názory pro