SQL Server查询表信息时多索引返回重复记录 取单条最大值实现问询

实现思路 #

• 原查询返回多条同表记录的原因是系统视图sys.dm_db_index_usage_stats按索引维度存储统计数据，同一张表的多个索引会对应多条记录
• 只需将原查询结果按表名分组，对表最后更新时间、表最后查询时间两个字段取最大值即可实现单表单记录，SQL Server启动时间为全局统一值，聚合操作不影响最终结果

修改后查询语句 #

SELECT 
    TableName,
    MAX(Last_Update) AS Last_Update,
    MAX(Last_Used) AS Last_Used,
    MAX(SQL_Server_Start_Time) AS SQL_Server_Start_Time
FROM (
    SELECT 
        OBJECT_NAME(OBJECT_ID) AS TableName,
        last_user_update AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS Last_Update,
        (
            SELECT MAX(Last_Used_Date)
            FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(Last_Used_Date)
        ) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS Last_Used,
        (
            SELECT [sqlserver_start_time] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'
            FROM sys.dm_os_sys_info
        ) AS SQL_Server_Start_Time
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID('DATABASENAME')
) AS TableUsageRawStats
GROUP BY TableName
ORDER BY TableName ASC

可选优化 #

如果需要覆盖从未产生过访问、更新记录的表，可增加和sys.tables系统表的左关联，避免遗漏数据库中存在但没有使用记录的表。

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