Query to find certain text used in sql server objects

gistfile1.sql

-- Set search text
DECLARE @searchTerm varchar(MAX)
SET @searchTerm = '<SEARCH_TERM>' 

-- Stored Procedures
SELECT OBJECT_NAME(M.object_id) as sp_name, M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE @searchTerm

-- User Defined Functions
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE @searchTerm
AND ROUTINE_TYPE='FUNCTION'
ORDER BY ROUTINE_NAME

-- Triggers
SELECT OBJECT_NAME(id) AS trigger_name
FROM syscomments
WHERE [text] LIKE @searchTerm AND OBJECTPROPERTY(id, 'IsTrigger') = 1
GROUP BY OBJECT_NAME(id)

-- Find a column used in all tables
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE @searchTerm
ORDER BY schema_name, table_name;