Many times during SQL server development we need to search for a stored procedure containing a specific text. This helps in checking for dependencies for objects in stored procedures or sometimes we might be simply interested in searching for a hard coded text. We can use the below approaches for finding the same:

1) Using INFORMATION_SCHEMA.ROUTINES

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

2) Using SYSCOMMENTS

    SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%SearchText%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

3) Using SYS.SQL_MODULES

    SELECT OBJECT_NAME(object_id)
    FROM SYS.SQL_MODULES
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%SearchText%'

In the below query the three approaches are combined into one single query :

SELECT DISTINCT [StoredProcedureName] 
FROM (
    (SELECT ROUTINE_NAME [StoredProcedureName]
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%' + @Search + '%' 
        AND ROUTINE_TYPE='PROCEDURE')
    UNION ALL
    (SELECT OBJECT_NAME(id) [StoredProcedureName]
        FROM SYSCOMMENTS 
        WHERE [text] LIKE '%' + @Search + '%' 
        AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY OBJECT_NAME(id))
    UNION ALL
    (SELECT OBJECT_NAME(object_id) [StoredProcedureName]
        FROM sys.sql_modules
        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
        AND definition LIKE '%' + @Search + '%')
) AS T
ORDER BY T.[StoredProcedureName]

Hope the above article helps you in solving a very common but equally important problem we encounter during SQL server development.