Search all databases for an object using sp_MSforeachdb

I often need to search an entire database for the existence of a database object.  Typically a table or view that is used across databases.

The script below uses sp_MSforeachdb (1):

       
use master
go

declare @sqlstmt varchar(1000);
select @sqlstmt = 
         'use [?]  select  ''[?]'' as [DBName],'
       + ' SCHEMA_NAME(o.schema_id ) as [schema_name], OBJECT_NAME(m.object_id) as [ObjectName]  /* ,m.[definition] */'
       + ' from sys.sql_modules as m ' 
       + ' inner join sys.all_objects as o on m.object_id = o.object_id ' 
       + ' where definition like ''%mydatabaseobject%''; '

select @sqlstmt;

exec sp_MSforeachdb @sqlstmt;

       
 

(1) There is a lot of debate about sp_MSforeachdb, along with some alternative procedures.
You can read about them here:

http://www.sqlservercentral.com/articles/sp_msforeachdb/117654/
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx