SQL Server Search Stored Procedure Text: A Complete Guide
Searching through stored procedure text in SQL Server is a critical skill for database administrators, developers, and analysts who need to locate specific code, troubleshoot issues, or audit database objects. Whether you're tracking down a reference to a deprecated table, verifying that a security patch has been applied across all procedures, or simply trying to understand how a particular business rule is implemented, the ability to efficiently search stored procedure text can save hours of manual work.
SQL Server stores the definition of every stored procedure as text in system catalog views, making it possible to query this metadata using standard T-SQL commands. That said, the most reliable and modern approach uses the sys. sql_modules catalog view, which contains one row for each module—including stored procedures, functions, triggers, and views—and exposes the full Transact-SQL source code in the definition column.
Why Search Stored Procedure Text?
There are several compelling reasons to search through stored procedure text:
- Troubleshooting: Quickly locate where a specific table, column, or function is referenced when debugging errors.
- Auditing: Verify that all stored procedures comply with coding standards or security policies.
- Refactoring: Identify every procedure that references an object before renaming or removing it.
- Documentation: Understand how business logic is distributed across multiple procedures.
Using sys.sql_modules to Search Stored Procedure Text
The sys.sql_modules view is the recommended starting point for searching stored procedure text. It provides a clean interface to the underlying source code and works consistently across all supported versions of SQL Server That's the whole idea..
Basic Syntax
To search for a keyword within all stored procedures, use the following query structure:
SELECT
OBJECT_NAME(object_id) AS ProcedureName,
definition AS ProcedureText
FROM sys.sql_modules
WHERE definition LIKE '%YourSearchTerm%'
Replace YourSearchTerm with the text you want to find. As an example, to find all stored procedures that reference the Orders table:
SELECT
OBJECT_NAME(object_id) AS ProcedureName,
definition AS ProcedureText
FROM sys.sql_modules
WHERE definition LIKE '%Orders%'
Filtering for Stored Procedures Only
By default, sys.sql_modules includes all programmable objects. To restrict results to stored procedures only, join with `sys.
SELECT
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ProcedureText
FROM sys.sql_modules sm
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE o.type = 'P'
AND sm.definition LIKE '%Orders%'
The type = 'P' condition ensures that only SQL Server stored procedures are returned, excluding functions, triggers, and views Less friction, more output..
Alternative Approaches
While sys.sql_modules is the preferred method, When it comes to this, alternative ways stand out.
Using INFORMATION_SCHEMA.ROUTINES
The INFORMATION_SCHEMA.ROUTINES view provides a standards-compliant way to access routine metadata, though it does not expose the full source code:
SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%Orders%'
Note that ROUTINE_DEFINITION may be truncated in some environments, so sys.sql_modules is generally more reliable for retrieving complete procedure text.
Using OBJECT_DEFINITION()
For retrieving the text of a single known stored procedure, the OBJECT_DEFINITION() function is useful:
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.GetCustomerOrders'))
This approach is efficient when you already know the exact name of the procedure you want to inspect Worth keeping that in mind. But it adds up..
Using sys.procedures with sys.sql_modules
Another common pattern combines sys.procedures with sys.sql_modules for clarity:
SELECT
p.name AS ProcedureName,
m.definition AS ProcedureText
FROM sys.procedures p
INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE m.definition LIKE '%Orders%'
This query explicitly leverages the sys.procedures view, which contains one row for each stored procedure in the database.
Advanced Search Techniques
Case-Sensitive Searches
By default, LIKE operations follow the database's collation settings. To perform a case-sensitive search regardless of collation, use COLLATE:
SELECT
OBJECT_NAME(object_id) AS ProcedureName,
definition AS ProcedureText
FROM sys.sql_modules
WHERE definition COLLATE Latin1_General_CS_AS LIKE '%Orders%'
Replace Latin1_General_CS_AS with a case-sensitive collation appropriate for your environment.
Searching for Multiple Terms
To find procedures that reference multiple terms, combine conditions with AND or OR:
SELECT
OBJECT_NAME(object_id) AS ProcedureName,
definition AS ProcedureText
FROM sys.sql_modules
WHERE definition LIKE '%Orders%'
AND definition LIKE '%CustomerID%'
Using CHARINDEX for Exact Matches
For more precise control over matching, CHARINDEX can be used instead of LIKE:
SELECT
OBJECT_NAME(object_id) AS ProcedureName,
definition AS ProcedureText
FROM sys.sql_modules
WHERE CHARINDEX('Orders', definition) > 0
Performance Considerations
Searching stored procedure text can be resource-intensive, especially in databases with many objects. Consider these optimization strategies:
- Limit result sets: Add additional filters such as schema name or creation date to reduce the number of rows scanned.
- Use full-text search: For large databases, enable full-text indexing on the
definitioncolumn to improve search performance significantly. - Avoid leading wildcards: Queries using
LIKE '%term%'cannot apply indexes effectively. If possible, restructure searches to avoid leading wildcards.
Example with Additional Filters
SELECT
OBJECT_NAME(sm.object_id) AS ProcedureName,
OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
sm.definition AS ProcedureText
FROM sys.sql_modules sm
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE o.type = 'P'
AND o.schema_id = SCHEMA_ID('dbo')
AND sm.definition LIKE '%Orders%'
ORDER BY o.modify_date DESC
This query limits results to stored procedures in the dbo schema and orders them by modification date, making it easier to focus on recently changed objects.
Common Use Cases
Finding References to a Specific Table
When preparing to rename or archive a table, it's essential to identify every stored procedure that references it:
SELECT DISTINCT
OBJECT_NAME(sm.object_id) AS ProcedureName
FROM sys.sql_modules sm
WHERE sm.definition LIKE '%SalesOrderDetail%'
Locating Hardcoded Values
Security audits often require finding procedures that contain hardcoded credentials or sensitive values:
SELECT
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ProcedureText
FROM sys.sql_modules sm
WHERE sm.definition LIKE '%password%'
OR sm.definition LIKE '%secret%'
Identifying Deprecated Features
Before upgrading SQL Server, search for usage of deprecated features:
SELECT
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ProcedureText
FROM sys.sql_modules sm
WHERE sm.definition LIKE '%TEXTPTR%'
OR sm.definition LIKE '%UPDATETEXT%'
Frequently Asked Questions
Q: Can I search for stored procedures across multiple databases?
Yes, you can query sys.sql_modules in each database individually or use linked servers to consolidate results. On the flip side, there is no built-in cross-database search mechanism in a single query.
Q: Is the definition column always complete?
In sys.sql_modules, the definition column contains the full source text. Even so, INFORMATION_SCHEMA.Plus, rOUTINES may truncate the definition, so sys. sql_modules is recommended for complete results Small thing, real impact. But it adds up..
Q: How do I handle special characters in search terms?
Escape special characters used in LIKE patterns, such as % and _, using brackets. Here's one way to look at it: to search for a literal percent sign: WHERE definition LIKE '%100[%]'
Q: Can I search for stored procedures by parameter name?
