Sql Find Stored Procedure With Text

Finding a stored procedure that contains a particular string of text is a routine yet critical task for database administrators, developers, and analysts. But whether you are troubleshooting a bug, auditing security permissions, or planning a refactor, being able to locate every procedure that references a table, column, or keyword can save hours of manual searching. This guide shows you multiple, reliable ways to search the definition of stored procedures in SQL Server using T‑SQL queries, SQL Server Management Studio (SSMS) features, and optional automation techniques. Each method is explained with clear examples, performance considerations, and best‑practice tips so you can choose the approach that fits your environment and workflow.

Why Search Stored Procedure Definitions?

Stored procedures encapsulate business logic, and over time they accumulate references to tables, columns, error messages, or even hard‑coded strings. Locating these references helps you:

• Impact analysis – Determine which procedures will be affected by a schema change (e.g., dropping a column).
• Debugging – Find where a particular error message or log statement is raised.
• Security review – Spot procedures that dynamically build SQL with user input, a potential injection vector.
• Code cleanup – Identify dead or obsolete code fragments before a refactor.
• Compliance – Verify that no procedure contains disallowed keywords or hard‑coded connection strings.

Because the definition of a stored procedure is stored as plain text in the database, you can search it just like any other string—provided you know where to look.

Method 1: Querying sys.sql_modules

The sys.sql_modules catalog view holds the definition (definition column) of every SQL module, including stored procedures, functions, triggers, and views. Because of that, joining it to sys. objects lets you filter by object type Most people skip this — try not to..

Basic Query

SELECT
    OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName,
    OBJECT_NAME(m.object_id)        AS ProcedureName,
    m.definition                    AS ProcedureDefinition
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE o.type = 'P'                     -- P = stored procedure
  AND m.definition LIKE '%YOUR_SEARCH_TEXT%';

Replace YOUR_SEARCH_TEXT with the string you are looking for.
The LIKE operator is case‑insensitive by default in a case‑insensitive collation; if your database uses a case‑sensitive collation, add COLLATE Latin1_General_CS_AS after the column.

Performance Tips

• Filter early – The join to sys.objects restricts the scan to procedures only, reducing rows examined.
• Avoid leading wildcards – If you can anchor the search (e.g., LIKE 'SELECT %'), SQL Server can use an index on definition if one exists (full‑text index is better for large texts).
• Use WHERE before SELECT – The query optimizer will push the predicate down, so only matching definitions are returned.

Example: Find All Procedures That Reference the Table Orders

SELECT
    OBJECT_SCHEMA_NAME(m.object_id) AS Schema,
    OBJECT_NAME(m.object_id)        AS ProcedureName
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE o.type = 'P'
  AND m.definition LIKE '%Orders%';

Method 2: Using the OBJECT_DEFINITION Function

OBJECT_DEFINITION(object_id) returns the definition of a given object as an nvarchar(max). Practically speaking, it can be used directly in a WHERE clause without joining to sys. sql_modules.

Query

SELECT
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id)        AS ProcedureName,
    OBJECT_DEFINITION(object_id)  AS ProcedureDefinition
FROM sys.objects
WHERE type = 'P'                              -- stored procedure
  AND OBJECT_DEFINITION(object_id) LIKE '%YOUR_SEARCH_TEXT%';

When to Prefer This Method

• Simplicity – Fewer joins, easier to read for quick ad‑hoc checks.
• Dynamic SQL – You can embed the function inside a larger dynamic query that builds the search term at runtime.

Caveat

OBJECT_DEFINITION returns NULL for objects that are not SQL modules (e., extended stored procedures). That's why g. The type = 'P' filter already guarantees you are dealing with a T‑SQL procedure, so the result is safe That's the whole idea..

Method 3: Searching via INFORMATION_SCHEMA.ROUTINES

The ANSI‑standard INFORMATION_SCHEMA views are portable across many RDBMS platforms. Day to day, in SQL Server, INFORMATION_SCHEMA. ROUTINES contains the ROUTINE_DEFINITION column for stored procedures and functions.

Query

SELECT
    ROUTINE_SCHEMA   AS SchemaName,
    ROUTINE_NAME     AS ProcedureName,
    ROUTINE_DEFINITION AS ProcedureDefinition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_DEFINITION LIKE '%YOUR_SEARCH_TEXT%';

Pros and Cons

• Pros – Easy to read, works similarly in MySQL and PostgreSQL (with minor syntax tweaks).
• Cons – The ROUTINE_DEFINITION column is limited to the first 4000 characters in older SQL Server versions; newer versions (2012+) return the full definition, but if you run an older build you might miss matches that appear after the 4000‑character mark. For complete safety, prefer sys.sql_modules or OBJECT_DEFINITION.

Method 4: Using SQL Server Management Studio (SSMS) GUI

If you prefer a point‑and‑click approach, SSMS provides a built‑in filter for object names and, starting with SSMS 18, a Search feature that scans object text.

Steps

1. Open Object Explorer and expand your database → Programmability → Stored Procedures.
2. Right‑click the Stored Procedures folder and choose Filter → Filter Settings….
3. In the Filter dialog, set Property to Text (or Definition depending on SSMS version) and Operator to Contains.
4. Enter your search string and click OK.
5. The list will now show only procedures whose definition contains the text.

Advantages

Advantages

• No coding required – Ideal for quick visual searches without writing queries.
• Real-time browsing – Results update instantly as you modify the filter criteria.
• Integration – You can right-click results to script or modify procedures directly.

Limitations

• Not automatable – Cannot be scheduled or embedded in scripts.
• Performance – Scanning text definitions can be slow on databases with thousands of procedures.
• Version dependency – The "Search" feature requires SSMS 18+; older versions only support name-based filtering.

Choosing the Right Approach

Conclusion

Searching for text within stored procedures is a common troubleshooting task, whether you're tracking down a specific column reference, verifying business logic, or auditing security settings. Each method offers distinct trade-offs between convenience, portability, and completeness Small thing, real impact. Which is the point..

For most DBAs, **OBJECT_DEFINITION

OBJECT_DEFINITION paired with sys.Here's the thing — sql_modules strikes the best balance—it returns the full, untruncated definition, performs reliably across all supported SQL Server versions, and fits neatly into reusable scripts or dynamic SQL. Plus, reserve the INFORMATION_SCHEMA view for cross-platform codebases, and lean on the SSMS GUI only for one-off, interactive investigations. By matching the tool to the task, you’ll locate the exact routine you need without wading through false positives or truncated code Worth keeping that in mind..