Find text in any SQL object

I needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn’t find one when I searched my site the other day.

It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @Search varchar(255)
SET @Search='PM00200'

SELECT DISTINCT
	['SQL Objects'].name AS Object_Name
	,['SQL Objects'].type_desc
FROM
	sys.sql_modules AS ['SQL Modules']
INNER JOIN
	sys.objects AS ['SQL Objects']
		ON
			['SQL Objects'].object_id=['SQL Modules'].object_id
WHERE
	['SQL Objects'].name LIKE 'u%_AZRCRV_%'
AND
	['SQL Modules'].definition LIKE '%'+@Search+'%'
ORDER BY
	['SQL Objects'].name
	,['SQL Objects'].type_desc