SQL Server中能否用SQL查询关联多租户数据库与主库表？

可以仅通过SQL查询在SQL Server中实现这个需求，核心是利用动态SQL来动态遍历所有租户数据库并拼接查询语句，具体实现步骤如下：

实现思路 #

由于租户数据库数量不固定（n个），静态SQL无法提前指定所有租户库的关联逻辑，因此需要从main库的tenants表中获取所有租户库名，动态生成跨库查询语句，最后通过UNION ALL合并所有租户的结果。

具体SQL脚本 #

在main数据库中执行以下脚本：

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

-- 拼接每个租户库的查询语句
SELECT @DynamicSQL += N'
SELECT 
    t.tenant_name,
    t.tenant_db,
    s.value AS date
FROM 
    main.dbo.tenants t
JOIN 
    [' + t.tenant_db + N'].dbo.settings s 
    ON s.[key] = ''DATE''
WHERE 
    t.tenant_db = ''' + t.tenant_db + N'''' + CASE WHEN LEAD(t.tenant_db) OVER (ORDER BY t.tenant_db) IS NOT NULL THEN N' UNION ALL ' ELSE N'' END
FROM 
    main.dbo.tenants t;

-- 执行动态生成的SQL
EXEC sp_executesql @DynamicSQL;

关键说明 #

• 跨库访问语法：通过[租户库名].dbo.settings的形式直接访问其他数据库中的表，确保租户库名与tenants表的tenant_db字段完全匹配。
• 权限要求：执行该查询的数据库账号必须拥有main库tenants表的读取权限，以及所有租户库中settings表的读取权限。
• 特殊字符处理：如果租户库名包含空格、连字符等特殊字符，方括号[]的包裹能避免语法错误。

内容的提问来源于stack exchange，提问作者jeremy__