SQL Server: Quickly search for objects in all databases

Use the code below to quickly search for database objects in all the instance databases. Just change the @filter text to match your needs.
 

declare @sqlstr nvarchar(1000)
declare @filter nvarchar(50)

select @filter = '%DTS%'

create table #tbldbobjects
  (
     dbname    sysname,
     objname   varchar(200),
     objtype   char(2),
     objuserid int
  )

select @sqlstr = 'sp_msforeachdb ''IF DB_ID(''''?'''') > 4 
                    Insert into #tblDBObjects 
                    select ''''?'''' as DBName, name, xtype, uid 
                    From ?..sysobjects where name like ''''' + @filter + ''''''''

exec sp_executesql @sqlstr

select *
from   #tbldbobjects
order  by dbname,
          objname

drop table #tbldbobjects 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s