How to find all Stored Procedures having a given text inside

To find all stored procedures containing a specific text in SQL Server, you can query the system catalog views.

Use sys.sql_modules

The most reliable way is using sys.sql_modules.

SELECT 
    o.name AS ProcedureName,
    m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.type = 'P'  -- Stored Procedures
AND m.definition LIKE '%your_text_here%';

Warning

Case Sensitivity

• Depends on your database collation.
• If needed, force case-insensitive search:

Notes

• This only works for non-encrypted stored procedures.
• Encrypted procedures won’t appear in definition.
• Works on SQL Server 2005 and later.

Use INFORMATION_SCHEMA.ROUTINES

If you want to search for all stored procedures having a given text inside, you may use the following script:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search%'
AND ROUTINE_TYPE='PROCEDURE'

Warnings

Definition may be truncated

ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES:

• Often cuts off long procedures
• You might miss matches if the text is beyond the truncated part

This is the biggest drawback.

Use syscomments

This is the equivalent of running the following query directly against the system tables/views:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%KEYWORD%'
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

Warnings

1. syscomments.text is split into chunks

SQL Server stores procedure definitions in multiple rows in syscomments.

That means:

• Your keyword might be split across two rows
• Example: 'KEY' in one row and 'WORD' in the next
• Result → you miss it entirely

2. Deprecated system tables

• sys.sysobjects and sys.syscomments are old compatibility views
• Microsoft recommends using modern catalog views instead
• They may not behave consistently in newer versions

3. Duplicate / partial results

• You’ll often get multiple rows per procedure
• Each row is just a fragment of the full definition

Check also FUNCTION types

The INFORMATION_SCHEMA.ROUTINES view can be used to search for content of functions as well.  Simply alter your WHERE clause as follows, substituting your search string in place of "KEYWORD" once again:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%'
AND ROUTINE_TYPE='FUNCTION'
ORDER BY ROUTINE_NAME