SSMS Search Stored Procedure Content

There are sometimes occasions where you might want to look through your stored procedures for references to tables or other database assets because you don't have direct access to script them yourself. Here's a quick script to let you do that, it can be adapted to do a few things which I'll add later:

DECLARE @SearchVal AS VARCHAR(250) = 'name'

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE m.definition Like '%' + @SearchVal + '%'