Skip to content

view-the-definition-of-a-stored-procedure.md

Latest commit

 

History

History
104 lines (72 loc) · 4.58 KB

view-the-definition-of-a-stored-procedure.md

File metadata and controls

104 lines (72 loc) · 4.58 KB
title View the definition of a stored procedure
description Learn 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.
author WilliamDAssafMSFT
ms.author wiassaf
ms.date 07/19/2024
ms.service sql
ms.subservice stored-procedures
ms.topic how-to
ms.custom
ignite-2025
helpviewer_keywords
stored procedures [SQL Server], viewing
definition of stored procedure
viewing stored procedures
displaying stored procedures
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

View the definition of a stored procedure

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]

View the definition of a stored procedure

This article describes how to view the definition of procedure in Object Explorer or T-SQL.

Use SQL Server Management Studio

To view the definition a procedure in Object Explorer:

  1. In Object Explorer, connect to an instance of [!INCLUDE ssDE] and then expand that instance.

  2. Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. 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.

  4. Select New Query Editor Window. This will display the procedure definition.

Use Transact-SQL

In T-SQL, you can use one of the following three commands:

Note

The system stored procedure sp_helptext is not supported in Azure Synapse Analytics. Instead, use sys.sql_modules object catalog view.

Use sp_helptext

  1. In Object Explorer, connect to an instance of the [!INCLUDE ssDE].

  2. On the toolbar, select New Query.

  3. 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';

Use OBJECT_DEFINITION

  1. In Object Explorer, connect to an instance of the [!INCLUDE ssDE].

  2. On the toolbar, select New Query.

  3. 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. This query leverages OBJECT_ID and OBJECT_DEFINITION to identify the object by its three-part name.

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

Use sys.sql_modules

  1. In Object Explorer, connect to an instance of the [!INCLUDE ssDE].

  2. On the toolbar, select New Query.

  3. 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'));

Related content