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?
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.