SQL Server cross database query when the other DB name is not known in advance?

Question

Given a multi-tenant setup with one database that holds the tenant data, and one database for each tenant, how can you construct a query to look up the same piece of data in each tenant database?

eg The tenant master database has a table called Tenants:

[TenantMaster].[dbo].[Tenants]
Id, Name, DatabaseName
1, Bob, bobs_db_name
2, Kate, kates_db_name

And each individual tenant has a table called Widgets:

[bobs_db_name].[dbo].[Widgets]
Id, Name
1, Red widget

Is it possible to write a select query to get the count of widgets in each tenant DB? Or does it require a cursor or a store procedure? eg the output would be:

Tenant, WidgetCount
Bob, 10
Kate, 12

Answer 1

You would need to use dynamic SQL if you have a list of tenants which will grow / shrink over time. Alternatively you could, when tenants are added or deleted, use dynamic SQL to generate views which reference each of the databases in question.

If you want to look in a specific database, but you don't know where to target that database yet, then you can use a synonym to achieve a similar result, as synonym targets are bound at execute time, and not create time.

Answer 2

In a multi-tenancy setup, I'd recommend considering caching these "across-the-board" stats in a central database and updating it periodically

e.g. a table in the master with following columns:
TenantId
WidgetCount
AsOfDate

That way, you can query the stats very quickly without hitting each database (but obviously the data may be out of date depending on the frequency of the update).

For scaling in a multi-tenancy system, I recommend this approach. But it does depend (as always) on exact requirements/whether you can live with slightly out of date stats etc.