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: