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 '%xxxx%';