Microsoft SQL select from X tables

Question

我需要从所有表中选择一行，但没有定义表名和表的数量?比如SELECT * FROM * WHERE id = 'test'

这可能吗?

Answer 1

您需要使用系统表构建一个动态查询并执行它

DECLARE @sql nvarchar(max) = (
SELECT STRING_AGG(CAST(
'SELECT *
FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + '
WHERE id = ''test'';'
AS nvarchar(max)), '
'      )
FROM sys.tables t
WHERE t.object_id IN (
SELECT c.object_id
FROM sys.columns c
WHERE c.name = 'id'
)
);
EXEC sp_executesql @sql;