Find a database with a particular table OR Find a table in every database of SQL Server

Question

I have a SQL Server with hundreds of databases and each database having hundreds of tables. Now I would like to find where in these databases is a table that I am looking for.

I could find if a table existed in individual database using

use myDatabase 
select * from sys.tables  where name = 'mytable' 
GO

but using this means I have to manually change the database for hundreds of times . I would like to find the database name only. Is there a way out ?

Answer 1

SELECT DISTINCT  DB_NAME(database_id)
FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL) 
WHERE OBJECT_NAME(object_id,database_id) = 'mytable'

Answer 2

Okay, if you're just wanting to find each database that contains a particular table, and aren't going to be querying the table, then you can just do:

create table #t (
    DBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t

(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_ID call, otherwise I use it to avoid finding tables in the wrong schema)