declare @term varchar(2000)
select @term = REPLACE('CROSS JOIN', '%', '\%')

SELECT ROUTINE_NAME, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) AS CharacterIndex,
	SUBSTRING(ROUTINE_DEFINITION, PATINDEX('%' + @term + '%', ROUTINE_DEFINITION) - 20, 40 + LEN(@term)) AS MatchingCode
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%' + @term + '%'
	AND ROUTINE_TYPE='PROCEDURE'

Finds all procedures that contain any occurrence of a string.  The results show the name of the procedure, the character index where the first match was found along with the portion of the code that matched the search term.

Search stored procedures for the occurance of a string

SQL