Select databases which only contain specific table

A concise way that brings them all back in one resultset is

SELECT nameFROM   sys.databasesWHERE  CASE         WHEN state_desc = 'ONLINE'               THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[mytable]', 'U')       END IS NOT NULL 

You can use sp_Msforeachdb that is an undocumented Stored procedure and run on all databases :

EXEC sp_Msforeachdb "use [?];select * from sys.tables where name='MYTable' "

more about sp_msforeachtable : The undocumented sp_MSforeachdb procedure

Return list of databases, which contains the specified table -

Query:

DECLARE       @SQL NVARCHAR(MAX)    , @table_name SYSNAMESELECT @table_name = 'dbo.test'IF OBJECT_ID('tempdb.dbo.#db_temp') IS NOT NULL    DROP TABLE #db_temp CREATE TABLE #db_temp (db_nm SYSNAME)SELECT @SQL = (    SELECT '        USE [' + d.name + '];         INSERT INTO #db_temp (db_nm)        SELECT DB_NAME()        WHERE EXISTS(            SELECT 1            FROM sys.objects            WHERE [object_id] = OBJECT_ID(''' + @table_name + ''')                AND [type] = ''U''        )'    FROM sys.databases d    WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')        AND d.state_desc != 'OFFLINE'    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')EXEC sys.sp_executesql @SQLSELECT * FROM #db_temp dt

Output:

USE [...]; INSERT INTO #db_temp (db_nm)SELECT DB_NAME()WHERE EXISTS(    SELECT 1    FROM sys.objects    WHERE [object_id] = OBJECT_ID('...')        AND [type] = 'U')...