Find text in stored procedures or views

This is a handy query that I found that searches through your SQL Server database stored procedures or views for occurrences of the specified text.

For example, to search for a stored procedure with the text "foo", use the following:

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE lower([text]) LIKE '%foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)

To search through views, replace 'IsProcedure' with 'IsView'.