Search in object definitions

SearchInDefinitions.sql

DECLARE @SearchText varchar(100) = '%search-text%';

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where sm.definition like @SearchText collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO