I got a request from someone to find a certain word, but it has to be after the last WHERE clause, not in the SELECT list. This is not perfect – technically you could have a subselect with a where clause, and no actual WHERE clause in the main query. But most of the time, there will be a final WHERE clause, and this will pick up the search term after that final clause.
;WITH ObjectDefinition AS(
OBJECT_DEFINITION(OBJECT_ID) as Defn
WHERE OBJECT_DEFINITION(OBJECT_ID) like ‘%WHERE%SearchTerm%’
WHERE SUBSTRING(Defn, LEN(Defn) – PATINDEX(‘%EREHW%’, REVERSE(Defn)), 2000) like ‘%SearchTerm%’