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

SELECT ROUTINE_NAME, ROUTINE_TYPE, 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='FUNCTION'

Searches the code for all functions in the current database and returns results containing the function name, the character index where the first match was found, and a portion of the code where the match occurred.

Search functions for the occurance of a string

search

SQL