Finding text in SQL Server Objects By Querying System Tables

Back in the days of SQL Server 6.5 I developed a query to search stored procedures for a given string, which I used to email to myself on every assignment that I took up. It’s now well out of date (and I’ve lost the file) so I’ve decided to do a quick rewrite, this time including text from other types of objects such as views.