SQL Server如何批量查询各表last_modified最大值并汇总为单表

你不需要借助Excel做中转，直接通过动态SQL拼接查询逻辑，一次执行就能得到你要的统一汇总表，代码如下：

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

-- 为所有符合筛选规则的表拼接查询语句
SELECT @sql = @sql + N'
UNION ALL
SELECT 
    ''' + REPLACE(t.name, '''', '''''') + N''' AS TableName,
    MAX(last_modified) AS last_modified
FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'
WHERE deleted = ''N'''
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
-- 校验表包含所需字段，避免执行报错
WHERE EXISTS (
    SELECT 1 FROM sys.columns c 
    WHERE c.object_id = t.object_id AND c.name = 'last_modified'
)
AND EXISTS (
    SELECT 1 FROM sys.columns c 
    WHERE c.object_id = t.object_id AND c.name = 'deleted'
)
-- 完全保留你原有的表筛选规则
AND t.NAME NOT LIKE 'dt%' 
AND t.name NOT LIKE '%ml%' 
AND t.NAME NOT IN (
    'OrderHeaders', 'OrderHeaderExtendedText', 'OrderLIDetails', 
    'OrderLIDetailExtendedText', 'UserCustomerXRef', 'UserDetails', 
    'UserDetailExtendedText', 'UserTypeDescription'
)
AND p.rows <> 0 
AND i.OBJECT_ID > 255   
AND i.index_id <= 1
GROUP BY t.name, s.name;

-- 移除拼接语句开头多余的UNION ALL后执行
SET @sql = STUFF(@sql, 1, 11, N'');
EXEC sp_executesql @sql;

代码说明 #

• 完全复用了你之前写的表筛选逻辑，查询的表范围和你手动筛选的结果完全一致
• 新增了字段存在性校验，遇到没有last_modified或deleted字段的表会自动跳过，不会中断整个查询
• 用MAX(last_modified)替代你之前写的top 1 ... order by last_modified desc逻辑，返回结果完全一致，执行效率更高
• 用QUOTENAME包装架构名和表名，遇到带特殊字符、空格的表名也能正常执行
• 执行完成后直接返回两列结构的结果集：第一列是表名TableName，第二列是对应表符合条件记录的最大last_modified值，和你预期的汇总表结构完全匹配，不需要任何额外处理。

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