Searching for text in stored procedures and functions in SQL Server

The following query comes in handy when you need to find which user-defined stored procedures or function definitions contain a certain string.


SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%<StringToSearch>%' 


This will return a list of names and definitions of all stored procedures and functions whose definition contains the string <StringToSearch>. If you wish to limit the results to stored procedures only then append the clause:

    AND ROUTINE_TYPE='PROCEDURE'

Likewise, if you wish only functions to be returned then use:

    AND ROUTINE_TYPE='FUNCTION'