Search in all databases

if object_id('tempdb..##findsp') is not null drop table ##findsp
create table ##findsp (Base varchar(100), Scheme varchar(200), Name varchar(200), Type varchar(200))

exec sp_MSforeachdb '
use [?]

insert ##findsp
select ''?'', s.name, o.name, o.type_desc from sys.sql_modules m
join sys.objects o on m.object_id = o.object_id
join sys.schemas s on s.schema_id = o.schema_id
where lower(m.definition) like lower(''%XXXXXXXXXX%'')
order by 1,2,3,4
'

select * from ##findsp
drop table ##findsp