Search for text in stored procedures and functions
Query that searches the definitions of stored procedures and functions for a text string, using INFORMATION_SCHEMA.ROUTINES.
To find the stored procedures and functions of the current database whose definition contains a given text, search INFORMATION_SCHEMA.ROUTINES.
DECLARE @search VARCHAR(50)
SET @search = 'searchterm'
SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE @search
ORDER BY
ROUTINE_NAME
Notes
LIKEwithout%wildcards matches only an exact definition. To find the text anywhere in a definition, useSET @search = '%searchterm%'.ROUTINE_DEFINITIONis defined asnvarchar(4000), so long definitions are truncated. Usesys.sql_modules(columndefinition) for full text.- The definitions of encrypted modules are not readable, so they cannot be searched.
@searchisVARCHAR(50): a search string longer than 50 characters is truncated.