SQL Server 2008R2：如何列出全服务器所有对象及相关活动？

好的，针对你在SQL Server 2008 R2中想要追踪所有数据库对象的使用情况、进而清理未使用对象的需求，我给你整理了实用的方法和最优方案，分模块来说明：

1. 表和视图的最后访问/更新时间 #

SQL Server 2008 R2可以通过系统视图sys.dm_db_index_usage_stats间接推断表的访问时间，同时sys.tables里的modify_date能直接拿到表的最后更新时间。下面是一个综合查询，能列出所有用户自定义表和视图的相关时间：

SELECT 
    OBJECT_NAME(object_id) AS object_name,
    CASE WHEN type_desc = 'USER_TABLE' THEN '表' ELSE '视图' END AS object_type,
    (SELECT MAX(last_user_seek) FROM sys.dm_db_index_usage_stats s WHERE s.object_id = o.object_id) AS last_access_time,
    o.modify_date AS last_modify_time
FROM 
    sys.objects o
WHERE 
    type_desc IN ('USER_TABLE', 'VIEW')
ORDER BY 
    last_access_time DESC;

注意：sys.dm_db_index_usage_stats的数据会在SQL Server重启后重置，所以如果服务器刚重启过，数据会不准确，建议你在服务器稳定运行一段时间后再查询。另外，视图的访问时间依赖其关联表的索引使用情况，如果视图未用到索引，可能需要结合扩展事件来补充追踪。

2. 函数的最后调用时间 #

SQL Server 2008 R2没有直接记录函数调用时间的系统视图，但可以通过查询缓存的执行计划来间接获取：

SELECT 
    OBJECT_NAME(object_id) AS function_name,
    MAX(last_execution_time) AS last_call_time
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) st
WHERE 
    (OBJECTPROPERTY(OBJECT_ID(st.text), 'IsScalarFunction') = 1 
     OR OBJECTPROPERTY(OBJECT_ID(st.text), 'IsTableFunction') = 1)
    AND OBJECT_NAME(object_id) IS NOT NULL
GROUP BY 
    OBJECT_NAME(object_id)
ORDER BY 
    last_call_time DESC;

这个方法的局限性是只能捕捉到缓存中的查询计划，如果函数调用的计划被清理出缓存，就会丢失记录。如果需要长期稳定追踪，建议使用扩展事件监控函数调用事件，性能比SQL Server Profiler好很多。

3. 存储过程的最后执行时间 #

对于存储过程，可以用sys.dm_exec_procedure_stats获取缓存中的执行统计：

SELECT 
    OBJECT_NAME(object_id) AS procedure_name,
    last_execution_time AS last_run_time,
    execution_count AS total_runs
FROM 
    sys.dm_exec_procedure_stats
WHERE 
    database_id = DB_ID() -- 仅查询当前数据库，如需所有数据库可删除此条件
ORDER BY 
    last_execution_time DESC;

同样，这个视图的数据会在重启或计划缓存清理后丢失。长期监控的话，扩展事件是更可靠的选择，你可以创建针对sp_statement_completed事件的会话，过滤存储过程的执行记录。

仅仅靠追踪数据还不够，清理前一定要做好以下几步，避免误删：

• 延长观察周期：建议至少收集1-3个月的使用数据，确保确实是长期未使用的对象——有些对象可能是月度/季度任务才会用到。
• 检查依赖关系：删除任何对象前，必须确认没有其他对象依赖它。可以用以下查询获取依赖当前对象的实体：

  SELECT 
      referencing_schema_name, referencing_entity_name, referencing_class_desc
  FROM 
      sys.dm_sql_referencing_entities('dbo.YourObjectName', 'OBJECT');
  

• 备份对象定义：删除前先导出对象的定义备份，万一误删可以快速恢复。你可以用sp_helptext 'dbo.YourObjectName'获取定义，或者在SSMS里生成脚本。
• 逐步验证：先把疑似未使用的对象移到一个备用数据库（比如创建ArchiveDB），观察一段时间，确认没有业务影响后再彻底删除。
• 过滤系统对象：所有查询都要确保只处理用户自定义对象，避免误操作系统内置对象。

另外，如果你想自动化追踪，可以创建一个定期运行的SQL Agent作业，把上述查询的结果保存到专门的统计表里，这样即使SQL Server重启，历史数据也不会丢失。

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