How to View the Definition of a Stored Procedure

In this post, I’m sharing some SQL Scripts that will help you view the definition text of Stored Procedures and other objects in SQL Server.

Ways to Query Stored Procedure Text

Like a lot of tasks in MSSQL, there’s various ways we can get this information, including:
> sys.sql_modules: A catalog view that stores SQL language-defined module definitions, including Stored Procedures, Views, Functions, and Triggers.
> OBJECT_DEFINITION: A system function that retrieves the definition of a specified object.
> sp_helptext: A system procedure for listing the text of objects (not supported in Azure Synapse Analytics).

For this demo, we’ll focus on sys.sql_modules and OBJECT_DEFINITION, as both are supported in Azure and provide reliable results.

Additionally, you could achieve similar results using PowerShell scripts, but I’ll keep examples purely SQL-based.

Searching for Stored Procedure Text

The sys.sql_modules catalog view contains a row for each object that has a SQL language-defined module. This includes:
– Stored Procedures (P)
– Replication Filters (RF)
– Views (V)
– Triggers (TR)
– Scalar User-Defined Functions (FN)
– Inline Table-Valued Functions (IF)
– Table-Valued Functions (TF)
– Rules (R)

Even stand-alone default objects (D) have their definitions stored here. For a complete list of object types, refer to the type column in the sys.objects catalog view.

Example 1: Searching with sys.sql_modules

You can search for specific text within stored procedure definitions using this query:

-- Search for text within stored procedure definitions using sys.sql_modules
SELECT o.name  
FROM sys.sql_modules m  
INNER JOIN sys.objects o  
    ON m.object_id = o.object_id  
WHERE m.[definition] LIKE '%ErrorHandler%'  
AND o.type = 'P';  

This query returns the names of all stored procedures that contain the string ErrorHandler in their definitions.

📌 Real-Life Scenario: Imagine you’re migrating a database, and you want to identify all procedures referencing a legacy feature like a deprecated table or column. Searching with OBJECT_DEFINITION is a quick way to locate those references.

Example 2: Using OBJECT_DEFINITION

Alternatively, you can use the OBJECT_DEFINITION function directly to retrieve an object’s definition. For example:

-- get an objects definition
USE AdventureWorks2022;  
GO  
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.uspLogError'));  

Or, when combined with sys.sql_modules:

-- get definition of object
USE AdventureWorks2022;  
GO  
SELECT [definition]  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID(N'dbo.uspLogError');  

📌 Tip: Use OBJECT_DEFINITION for quick lookups and sys.sql_modules when you need to integrate with other metadata from the catalog view, such as modify_date or is_schema_bound.

That’s It!

That’s it for this quick post. For more information, check out the Microsoft documentation on querying stored procedure definitions, which includes T-SQL examples for the other two ways to get object definitions.