Search through stored procedures in SQL Server for a specific string of characters

While I have experience in designing and programming SQL databases, I don’t often deal with databases or SQL directly at work. Every so often, I find I need a nice query for doing something very specific within the SQL Server environment. Today I needed to find a specific column that was being used in our stored procedures because the table from which the column resided in was being removed from our database. The biggest problem we have (in my opinion) is numerous stored procedures (almost 600) so finding all references to the column by hand is out of the question. Of course, I would never want to manually search for anything to begin with but I digress. I found this nice query that will find any string of characters within your stored procedures.

SELECT OBJECT_NAME(object_id) as sproc, OBJECT_DEFINITION(object_id) as code
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'
    order by sproc asc

It’s very clear what this query is doing but without the knowledge of the built-in SQL functions like OBJECT_NAME() or OBJECT_DEFINITION(), it would’ve taken me a lot longer to create this query.