Posted on

Find a String in a Stored Procedure

Use this handy query to find all procedures containing the search string @Search

DECLARE @Search varchar(255)
SET @Search='Document'

SELECT DISTINCT
 o.name AS Object_Name,o.type_desc
 FROM sys.sql_modules m 
 INNER JOIN sys.objects o ON m.object_id=o.object_id
 WHERE m.definition Like '%'+@Search+'%'
 ORDER BY 2,1

Thanks to StackOverflow for this. Or alternatively make use of the information_schema:

SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION 
 FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_DEFINITION LIKE '%Document%'

Hat tip here.