Microsoft SQL如何不指定表名及数量从所有表中查询指定id的行

这个需求无法直接通过原生标准SQL实现，SQL规范不支持FROM子句后使用通配符*匹配所有表，必须显式指定查询的表名。但可以借助数据库的系统元数据表动态生成查询语句，间接达到需求，主流数据库的实现方案如下： #

MySQL 实现 #

先查询information_schema系统库的元数据，拼接得到所有包含id字段的表的查询语句，再执行动态SQL：

-- 可选：调大group_concat长度限制，避免表数量过多时语句被截断
SET SESSION group_concat_max_len = 1024000;

-- 拼接生成UNION ALL的查询语句
SELECT GROUP_CONCAT(
    'SELECT ''', TABLE_NAME, ''' AS matched_table, * FROM ', TABLE_NAME, ' WHERE id = ''test'' LIMIT 1'
    SEPARATOR ' UNION ALL '
) INTO @dynamic_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() -- 限定为当前操作的数据库，可自行修改为指定库名
  AND COLUMN_NAME = 'id';

-- 执行生成的SQL
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

执行后会返回所有匹配到数据的表，以及对应的行内容，每个表最多返回1条匹配结果。 #

PostgreSQL 实现 #

通过DO语句遍历所有带id字段的表执行查询：

DO $$
DECLARE
    table_rec record;
    data_rec record;
BEGIN
    -- 遍历public schema下所有带id字段的表
    FOR table_rec IN SELECT table_name FROM information_schema.columns
                     WHERE table_schema = 'public' AND column_name = 'id'
    LOOP
        -- 动态执行查询
        EXECUTE format('SELECT * FROM %I WHERE id = $1 LIMIT 1', table_rec.table_name)
        INTO data_rec
        USING 'test';
        -- 打印匹配到的结果
        IF data_rec IS NOT NULL THEN
            RAISE NOTICE '表名：%，匹配数据：%', table_rec.table_name, data_rec;
        END IF;
    END LOOP;
END $$;

其他数据库适配 #

SQL Server、Oracle等其他数据库的实现逻辑完全一致，只需要替换对应数据库的元数据表查询语句即可：

• SQL Server查询表名的系统视图为sys.tables、sys.columns
• Oracle查询表名的系统视图为ALL_TAB_COLUMNS

注意事项 #

• 该方案仅适合临时排查数据使用，不要写入生产业务代码，查询性能会随着表数量、数据量级上升明显下降
• 如果不需要限定查询字段为id，可以去掉元数据查询中COLUMN_NAME = 'id'的过滤条件，注意如果表没有对应的查询字段会触发报错

内容的提问来源于stack exchange，提问作者J. Doe