Introduction

The most reliable way to search for text inside stored procedures in SQL Server is to query sys.sql_modules joined with sys.procedures using a LIKE filter on the definition column. This returns the full procedure definition without truncation, unlike INFORMATION_SCHEMA.ROUTINES which caps the definition at 4,000 characters. This article covers both approaches, along with searching across all object types, filtering by schema, and handling encrypted procedures.

Method 1: sys.sql_modules + sys.procedures (Recommended)

This is the standard approach used by database administrators and the most reliable for production work:

1SELECT 
2    s.name AS SchemaName,
3    p.name AS ProcedureName,
4    p.create_date,
5    p.modify_date,
6    m.definition
7FROM 
8    sys.procedures p
9JOIN 
10    sys.sql_modules m ON p.object_id = m.object_id
11JOIN 
12    sys.schemas s ON p.schema_id = s.schema_id
13WHERE 
14    m.definition LIKE '%SearchText%'
15ORDER BY 
16    s.name, p.name;

Replace %SearchText% with the text you are looking for. The LIKE operator with wildcards on both sides searches anywhere within the procedure body.

Why sys.sql_modules Is Preferred

sys.sql_modules.definition stores the complete source code of the procedure with no length limit. It is an nvarchar(max) column, so even procedures with thousands of lines return their full text.

For a more targeted search that returns only the procedure name and line numbers where the match occurs:

1SELECT 
2    SCHEMA_NAME(p.schema_id) + '.' + p.name AS FullName,
3    m.definition
4FROM 
5    sys.procedures p
6JOIN 
7    sys.sql_modules m ON p.object_id = m.object_id
8WHERE 
9    m.definition LIKE '%deprecated_column%'
10    AND m.definition NOT LIKE '%--deprecated_column%'  -- Exclude commented references
11ORDER BY 
12    p.name;

Method 2: INFORMATION_SCHEMA.ROUTINES

This ANSI-standard view works across SQL Server, PostgreSQL, and MySQL, making it useful for cross-platform scripts:

1SELECT 
2    ROUTINE_SCHEMA,
3    ROUTINE_NAME,
4    ROUTINE_TYPE,
5    ROUTINE_DEFINITION
6FROM 
7    INFORMATION_SCHEMA.ROUTINES
8WHERE 
9    ROUTINE_DEFINITION LIKE '%SearchText%'
10    AND ROUTINE_TYPE = 'PROCEDURE';

The 4,000-Character Truncation Problem

ROUTINE_DEFINITION is defined as nvarchar(4000) in SQL Server. Any procedure longer than 4,000 characters has its definition truncated in this view. If your search text appears after the 4,000th character, this query will miss it entirely.

Compare the two approaches:

Method 3: Search Across All Object Types

Stored procedures are not the only objects that contain SQL code. To search functions, triggers, and views as well:

1SELECT 
2    o.type_desc AS ObjectType,
3    SCHEMA_NAME(o.schema_id) + '.' + o.name AS ObjectName,
4    o.create_date,
5    o.modify_date
6FROM 
7    sys.sql_modules m
8JOIN 
9    sys.objects o ON m.object_id = o.object_id
10WHERE 
11    m.definition LIKE '%SearchText%'
12ORDER BY 
13    o.type_desc, o.name;

The type_desc column tells you the object type:

Method 4: Search Using OBJECT_DEFINITION()

For quick ad-hoc searches, OBJECT_DEFINITION() returns the definition of a single object by its ID:

1-- Check a specific procedure
2SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.MyProcedure'));
3
4-- Search all objects using OBJECT_DEFINITION
5SELECT 
6    name,
7    type_desc
8FROM 
9    sys.objects
10WHERE 
11    OBJECT_DEFINITION(object_id) LIKE '%SearchText%'
12    AND type IN ('P', 'FN', 'IF', 'TF', 'V', 'TR');

This approach is concise but performs a function call per row, making it slower than the sys.sql_modules JOIN for large databases.

Method 5: Search with Context (Show Surrounding Lines)

When you need to see where the match occurs within the procedure, split the definition into lines:

1WITH ProcLines AS (
2    SELECT 
3        SCHEMA_NAME(p.schema_id) + '.' + p.name AS ProcName,
4        line.value AS LineText,
5        ROW_NUMBER() OVER (PARTITION BY p.object_id ORDER BY (SELECT NULL)) AS LineNum
6    FROM 
7        sys.procedures p
8    JOIN 
9        sys.sql_modules m ON p.object_id = m.object_id
10    CROSS APPLY 
11        STRING_SPLIT(m.definition, CHAR(10)) AS line
12    WHERE 
13        m.definition LIKE '%SearchText%'
14)
15SELECT 
16    ProcName,
17    LineNum,
18    LTRIM(RTRIM(LineText)) AS LineText
19FROM 
20    ProcLines
21WHERE 
22    LineText LIKE '%SearchText%'
23ORDER BY 
24    ProcName, LineNum;

This returns the specific line numbers and content where the search text appears, similar to grep -n on a filesystem.

Note: STRING_SPLIT does not guarantee order in SQL Server. The ROW_NUMBER() provides approximate line numbers, not guaranteed exact positions.

Searching Across Multiple Databases

To search all databases on a server, use sp_MSforeachdb or a cursor:

1-- Search across all user databases
2DECLARE @SearchText NVARCHAR(200) = '%deprecated_table%';
3DECLARE @SQL NVARCHAR(MAX);
4
5SET @SQL = '
6    USE [?];
7    SELECT 
8        DB_NAME() AS DatabaseName,
9        SCHEMA_NAME(p.schema_id) + ''.'' + p.name AS ProcName
10    FROM 
11        sys.procedures p
12    JOIN 
13        sys.sql_modules m ON p.object_id = m.object_id
14    WHERE 
15        m.definition LIKE ''' + @SearchText + '''
16';
17
18EXEC sp_MSforeachdb @SQL;

For a safer parameterized version that avoids SQL injection:

1CREATE TABLE #Results (
2    DatabaseName SYSNAME,
3    ProcName NVARCHAR(500)
4);
5
6DECLARE @db SYSNAME;
7DECLARE db_cursor CURSOR FOR
8    SELECT name FROM sys.databases 
9    WHERE state_desc = 'ONLINE' AND database_id > 4;
10
11OPEN db_cursor;
12FETCH NEXT FROM db_cursor INTO @db;
13
14WHILE @@FETCH_STATUS = 0
15BEGIN
16    DECLARE @sql NVARCHAR(MAX) = '
17        USE ' + QUOTENAME(@db) + ';
18        INSERT INTO #Results
19        SELECT DB_NAME(), SCHEMA_NAME(p.schema_id) + ''.'' + p.name
20        FROM sys.procedures p
21        JOIN sys.sql_modules m ON p.object_id = m.object_id
22        WHERE m.definition LIKE ''%deprecated_table%''';
23    
24    EXEC sp_executesql @sql;
25    FETCH NEXT FROM db_cursor INTO @db;
26END
27
28CLOSE db_cursor;
29DEALLOCATE db_cursor;
30
31SELECT * FROM #Results ORDER BY DatabaseName, ProcName;
32DROP TABLE #Results;

Handling Encrypted Procedures

Procedures created with WITH ENCRYPTION return NULL for their definition in both sys.sql_modules and INFORMATION_SCHEMA.ROUTINES. You cannot search their text through standard catalog views.

To identify encrypted procedures:

1SELECT 
2    SCHEMA_NAME(p.schema_id) + '.' + p.name AS ProcName,
3    CASE WHEN m.definition IS NULL THEN 'Encrypted' ELSE 'Readable' END AS Status
4FROM 
5    sys.procedures p
6LEFT JOIN 
7    sys.sql_modules m ON p.object_id = m.object_id
8WHERE 
9    m.definition IS NULL;

Third-party tools can decrypt these procedures, but in general, if you need to search procedure code, avoid WITH ENCRYPTION in your development workflow.

Case-Sensitive Searching

LIKE follows the database's default collation. In a case-insensitive database (the most common default), LIKE '%select%' matches SELECT, Select, and select. For explicit case-sensitive search:

WHERE m.definition LIKE '%SearchText%' COLLATE Latin1_General_BIN

Common Pitfalls

• Using INFORMATION_SCHEMA.ROUTINES for long procedures. The ROUTINE_DEFINITION column truncates at 4,000 characters. Always prefer sys.sql_modules for reliable full-text search.
• Forgetting to search functions and triggers. A table or column reference might appear in a trigger or function, not just stored procedures. Search sys.sql_modules joined with sys.objects to cover all module types.
• Not accounting for schema names. Procedures in non-default schemas (like sales.GetOrder) require schema qualification when reporting results. Always include SCHEMA_NAME(schema_id) in your output.
• Searching encrypted procedures and getting no results. Encrypted procedure definitions return NULL. The search silently skips them. Check for encrypted objects separately if completeness matters.
• LIKE with leading wildcard performance. LIKE '%text%' cannot use indexes and performs a full scan of every definition. On databases with thousands of procedures, this is usually still fast (seconds), but it does read every row.
• Forgetting dynamic SQL. Procedures that build SQL strings with EXEC or sp_executesql may reference tables and columns within string literals. A LIKE search finds these string matches, but the referenced objects are not visible as static dependencies.

Summary

• Use sys.sql_modules joined with sys.procedures for the most reliable stored procedure text search. It returns the full definition without truncation.
• Avoid INFORMATION_SCHEMA.ROUTINES for long procedures due to the 4,000-character limit on ROUTINE_DEFINITION.
• Join with sys.objects instead of sys.procedures to search across procedures, functions, triggers, and views simultaneously.
• Use STRING_SPLIT on the definition to find specific line numbers where the search text appears.
• For multi-database searches, use a cursor with QUOTENAME for safe dynamic SQL.
• Encrypted procedures return NULL definitions and cannot be searched through catalog views.