The RedGate tool is free so having access isn't really much of an issue. I point it out because many people have not heard of that issue and using an approach like described here leaves a person not realizing a database might have been skipped.

For the sake of completeness here is a working example of another approach to this type of search that I have in my examples. I modified my version to return the same columns as your fine example. By no means am I being critical of your fine post. Instead I am pointing out an often missed issue with the undocumented sp_MSforeachdb. I used to use that for things like this until it once missed a database and as such my results were incorrect. I decided at that point it was time to find alternative methods. Here is one such approach.


declare @search_string varchar(100) = 'mytext'
declare @sql nvarchar(max) = '' --need to set this to an empty string for the next statement to work correctly

select @sql = @sql + 'select DISTINCT ''' + db.name + ''' as DatabaseName, s.name AS Schema_Name, o.name AS Object_Name, o.type_desc
FROM ' + db.name + '.sys.sql_modules m
INNER JOIN ' + db.name + '.sys.objects o ON m.object_id = o.object_id
INNER JOIN ' + db.name + '.sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition Like ''%' + @search_string + '%'' UNION ALL
'
from sys.databases db
where db.database_id > 4 --eliminates master, tempdb, model, msdb

select @sql = STUFF(@sql, len(@sql) - 11, 11, '')

select @sql
--exec sp_executesql @sql --once you are comfortable with the dynamic sql you can uncomment this line