SQL to search database object's definitions

search_database_objects_definitions.sql

-- find objects which contain 'Pilots' in defintion
DECLARE @search VARCHAR(255)
SET @search='Pilots'

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 '%' + @search + '%'
ORDER  BY 2, 1