sql-server - Microsoft SQL 从 X 表中选择

Question

我需要从所有表中选择一行但不定义表名和表数量？就像是SELECT * FROM * WHERE id = 'test'

这可能吗？

标签： sql-server

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;