Display all the names of databases containing particular table

Question

I have many databases in my SQL Server.

I have to just search for database names containg particular table name Heartbitmaster

I have many databases such as Gotgold, DVD, etc and I just want to find database names from query that contain this table Heartbitmaster.

I searched I tried for query:

SELECT      TABLE_NAME   FROM      INFORMATION_SCHEMA.TABLES  WHERE      TABLE_TYPE = 'base table'        AND table_schema = 'Heartbitmaster' 

but it didn't work.

I searched further and came across:

SELECT name, database_id, create_date FROM sys.databases  

but dont know how to arrange further where condition for search of table name

Please help me.

Answer 1

I got it done through following query:

SELECT name FROM   sys.databases WHERE  CASE   WHEN state_desc = 'ONLINE'    THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[heartbit]', 'U')    END IS NOT NULL