Query SQL Server data dictionary to find all databases containing table 'x'

Question

I'm not overly familiar with the SQL Server data dictionary, but I've assumed what I'm trying to do is possible.

We have a number of replicated databases, under different names say: Client1 Client2 Client3

Rather than rely on a naming convention, I was hoping to identify these databases, based on whether they include a key table, call it MyTable. So started thinking a query such as the following was needed:

SELECT db.name
FROM sys.databases db
JOIN sys.tables tbl ON ??
WHERE tbl.Name = 'MyTable'

This doesn't work, as I can't see how to join sys.tables to sys.databases directly or indirectly, and also sys.tables is a view based on the active database, rather than a complete set of tables for all databases.

Can anyone identify a suitable query for this situation?

Answer 1

Try the undocumented sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 
'select table_catalog from 
information_schema.tables where table_name like ''MyTable%'''

The only way that comes to mind to do it as a query is to build the select statement dynamically (insert standard dynamic sql warning/disapproval here)

 Declare @SQL varchar(max)
    Set @SQL = ''
    Select @SQL = @SQL + Coalesce('Select Distinct 
    table_catalog from ' + name  + '.information_schema.tables 
    where table_name like ''mytable%''  UNION ','' )
    from sys.databases where state_desc = 'ONLINE'
    and collation_name =  'SQL_Latin1_General_CP1_CI_AS'
    set @SQL =  Substring(@SQL, 1, Len(@SQL) - 6) + ' order by table_catalog  '
    exec (@SQL)

*NOTE I added some criteria for the state and collation of the available databases.