如何在SQL中批量获取模式下所有表的MAX(last_updated_at)（无需大量UNION）

针对不同数据库系统，都可以通过查询系统元数据表生成动态SQL的方式解决，不用手动写几千个UNION语句，以下是主流数据库的具体实现：

MySQL/MariaDB #

方式1：生成拼接语句后手动执行 #

先运行下面的SQL，会输出拼接好的查询片段：

SELECT CONCAT(
    'SELECT ''', table_name, ''' AS `TABLE NAME`, MAX(last_updated_at) AS `MAX(LAST_UPDATED_AT)` FROM `', table_schema, '`.`', table_name, '`',
    CASE WHEN row_number() OVER (ORDER BY table_name) > 1 THEN ' UNION ALL ' ELSE '' END
)
FROM information_schema.columns
WHERE table_schema = '你的指定模式名' -- 替换为实际schema名称
  AND column_name = 'last_updated_at'
ORDER BY table_name;

把输出的所有行拼接成完整SQL，执行即可得到结果。

方式2：用存储过程自动执行（更高效） #

DELIMITER //
CREATE PROCEDURE GetMaxLastUpdated()
BEGIN
    DECLARE sql_stmt TEXT DEFAULT '';
    SELECT GROUP_CONCAT(
        CONCAT('SELECT ''', table_name, ''' AS `TABLE NAME`, MAX(last_updated_at) AS `MAX(LAST_UPDATED_AT)` FROM `', table_schema, '`.`', table_name, '`')
        SEPARATOR ' UNION ALL '
    ) INTO sql_stmt
    FROM information_schema.columns
    WHERE table_schema = '你的指定模式名'
      AND column_name = 'last_updated_at';
    
    SET @final_sql = sql_stmt;
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL GetMaxLastUpdated();

PostgreSQL #

利用string_agg自动拼接动态SQL并执行，%I会自动处理特殊表名：

DO $$
DECLARE
    sql_stmt TEXT;
BEGIN
    SELECT string_agg(
        format('SELECT ''%I'' AS "TABLE NAME", MAX(last_updated_at) AS "MAX(LAST_UPDATED_AT)" FROM %I.%I',
               table_name, table_schema, table_name),
        ' UNION ALL '
    ) INTO sql_stmt
    FROM information_schema.columns
    WHERE table_schema = '你的指定模式名' -- 替换为实际schema名称
      AND column_name = 'last_updated_at';
    
    EXECUTE sql_stmt;
END $$;

SQL Server #

通过系统视图生成动态SQL，自动拼接并执行：

DECLARE @sql_stmt NVARCHAR(MAX) = '';

SELECT @sql_stmt += CONCAT(
    'SELECT ''', QUOTENAME(table_name), ''' AS [TABLE NAME], MAX(last_updated_at) AS [MAX(LAST_UPDATED_AT)] FROM ', QUOTENAME(table_schema), '.', QUOTENAME(table_name), ' UNION ALL '
)
FROM information_schema.columns
WHERE table_schema = '你的指定模式名' -- 替换为实际schema名称
  AND column_name = 'last_updated_at';

-- 移除末尾多余的UNION ALL
SET @sql_stmt = LEFT(@sql_stmt, LEN(@sql_stmt) - 10);

EXEC sp_executesql @sql_stmt;

通用注意事项 #

• 务必替换代码中你的指定模式名为实际的数据库模式名称
• 确保执行账号拥有查询系统元数据以及所有目标表的权限
• 若部分表的last_updated_at字段允许为NULL，MAX函数会自动忽略NULL值

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