How to Search Stored Procedures by Text in SQL Server: A Step-by-Step Guide

 

-- Method 1: Using sys.procedures and sys.sql_modules to search for stored procedures by text

SELECT

    p.name AS ProcedureName,

    m.definition AS ProcedureDefinition

FROM sys.procedures p

INNER JOIN sys.sql_modules m ON p.object_id = m.object_id

WHERE m.definition LIKE '%your_search_text%';

-- Method 2: Using INFORMATION_SCHEMA.ROUTINES for a simpler approach

SELECT

    ROUTINE_NAME,

    ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE'

AND ROUTINE_DEFINITION LIKE '%your_search_text%';

-- Example: Replace 'your_search_text' with the text you want to search for, e.g., 'UPDATE Customer'

SELECT

    p.name AS ProcedureName,

    m.definition AS ProcedureDefinition

FROM sys.procedures p

INNER JOIN sys.sql_modules m ON p.object_id = m.object_id

WHERE m.definition LIKE '%UPDATE Customer%';

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone