SQL Server复制表最后同步日期查询及权限适配问题

使用SQL Server 2019 v15.0.4420.2，接收外部组织同步的大量下划线前缀命名的复制表，需要查询这些表的存在情况及最后刷新/更新/同步时间。现有同事提供的查询依赖sys.objects的modify_date字段，但因部分表无索引，且对复制场景下modify_date的含义存疑，同时想了解sp_replmonitorhelpsubscription的权限适用性。

原查询语句如下（已简化冗余部分）：

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    o.create_date, 
    o.modify_date,
    DATEDIFF(DAY, o.modify_date, GETDATE()) AS Age,
    CASE 
        WHEN DATEDIFF(DAY, o.modify_date, GETDATE()) > 7 
        THEN 'Out of Synch by ' + CAST(DATEDIFF(DAY, o.modify_date, GETDATE()) / 7 AS VARCHAR) + ' Weeks'
        ELSE 'Synched'
    END AS Evaluation
FROM 
    sys.objects o
WHERE 
    SCHEMA_NAME(o.schema_id) = 'dbo' 
    AND o.name LIKE '[_]%'
    AND o.type = 'U' -- 仅查询用户表
ORDER BY  
    Age DESC;

该查询的核心问题在于：modify_date无法反映复制数据的同步时间，它仅记录表结构（ALTER TABLE）、索引创建/修改、约束变更的时间，数据的插入/更新/删除（包括复制同步的操作）不会更新此字段。因此用它判断同步状态完全不可靠。

根据权限情况，可选择以下两种方案：

方案1：利用复制系统视图（推荐，需权限） #

订阅端数据库内置了复制相关系统视图，可直接查询同步状态，前提是DBA为你分配replmonitor数据库角色权限：

SELECT 
    s.publication AS 发布名称,
    a.article AS 复制表名,
    MAX(t.commit_time) AS 最后同步时间,
    DATEDIFF(DAY, MAX(t.commit_time), GETDATE()) AS 同步间隔天数,
    CASE 
        WHEN DATEDIFF(DAY, MAX(t.commit_time), GETDATE()) >7
        THEN '已滞后 ' + CAST(DATEDIFF(DAY, MAX(t.commit_time), GETDATE())/7 AS VARCHAR) + ' 周'
        ELSE '同步正常'
    END AS 同步状态
FROM MSreplication_subscriptions s
JOIN MSarticles a ON s.publication = a.publication AND s.publisher_db = a.publisher_db
LEFT JOIN MSrepl_transactions t ON s.publisher_db = t.publisher_db AND a.publication_id = t.publication_id
WHERE s.subscriber_db = DB_NAME()
  AND a.article LIKE '[_]%'
GROUP BY s.publication, a.article
ORDER BY 最后同步时间 DESC;

此查询直接获取复制事务的最后提交时间，是判断同步状态的准确依据。

方案2：基于表数据的时间字段（无复制权限时） #

若无法获取复制权限，可通过表内的业务时间字段（如LastUpdated）判断：

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    o.create_date,
    -- 替换为表内实际的更新时间字段
    (SELECT MAX(LastUpdated) FROM dbo.[_YourTableName]) AS 最后数据更新时间,
    DATEDIFF(DAY, (SELECT MAX(LastUpdated) FROM dbo.[_YourTableName]), GETDATE()) AS Age,
    CASE 
        WHEN DATEDIFF(DAY, (SELECT MAX(LastUpdated) FROM dbo.[_YourTableName]), GETDATE()) >7
        THEN '已滞后 ' + CAST(DATEDIFF(DAY, (SELECT MAX(LastUpdated) FROM dbo.[_YourTableName])/7 AS VARCHAR) + ' 周'
        ELSE '同步正常'
    END AS Evaluation
FROM sys.objects o
WHERE SCHEMA_NAME(o.schema_id) = 'dbo' 
  AND o.name LIKE '[_]%'
  AND o.type = 'U'
ORDER BY Age DESC;

如果没有时间字段，可尝试用sys.dm_db_index_usage_stats的last_user_update，但注意SQL Server重启后该值会重置，且无索引的堆表可能无记录。

在复制场景中，sys.objects.modify_date仅在以下场景更新：

• 执行ALTER TABLE修改表结构时
• 创建、修改、删除表的索引时
• 添加/删除表的约束（主键、外键等）时
  任何数据层面的操作（包括复制同步的增删改）都不会改变此字段的值。

• 该存储过程主要在发布端使用，用于监控订阅状态，订阅端需连接到发布端才能调用。
• 默认权限要求：发布端数据库的db_owner、replmonitor角色成员，或sysadmin服务器角色成员。
• 作为订阅端的业务只读用户，无法直接使用，需发布端DBA为你分配发布端数据库的replmonitor角色权限后才能调用。

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