SQL Server中怎么知道哪些表被访问过？

同事问了个问题，我需要知道SQL Server中的某个库都有哪些表被访问过，这个怎么实现？

SQL Server确实不太熟悉，如果是Oracle，我们可以通过AUDIT审计功能，实现表级、字段级这种粒度的监控，另外如果比较粗略的，还可以通过数据字典找到所有SELECT的语句，之所以说粗略，因为缓存是按照LRU算法存储的，如果数据库非常闲，他会记录很多执行过的语句，但如果数据库非常繁忙，很可能只存储了最近执行过的语句。

因此，相同的原理，我们能通过SQL Server的数据字典，找到一些执行过的语句，这就用到了sys.dm_exec_cached_plans和sys.dm_exec_sql_text，我们从微软的官方文档，可以了解视图的作用。

1. sys.dm_exec_cached_plans

a290cf4febb69db3b146d53460494355.png

P.S. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql?view=sql-server-ver15

2. sys.dm_exec_sql_text

bc2a3b96058388346c35ca470c1a9d22.png

P.S. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-ver15

sys.dm_exec_cached_plans存储的是查询计划，作用和Oracle的执行计划应该是相同的，通过复用，减少解析消耗，提高数据检索执行的效率。通过该视图的plan_handle，可以关联sys.dm_exec_sql_text，找到执行的SQL语句文本，再结合我需要找检索的SELECT关键字，就可以达到找出执行过的SELECT语句，进而知道那些表被访问了。

SQL如下，

SELECT p.refcounts, p.usecounts, sqltext.text
  FROM sys.dm_exec_cached_plans p
 CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) sqltext
 WHERE p.objtype IN ('Adhoc', 'Prepared') 
   AND p.cacheobjtype = 'Compiled Plan'
   AND sqltext.text NOT LIKE '%sys%'
   AND sqltext.text NOT LIKE '%fn_listextendedproperty%'
   AND upper(sqltext.text) LIKE 'SELECT%'
   AND upper(db_name(sqltext.dbid)) = @
 ORDER BY p.usecounts desc, p.refcounts desc;

相关的字段含义如下所示，

356300a02098471b261a79aacdc3e604.png

88ff3179da26604a058c078b70ef3f0f.png

7c8767e9508c68567ee78e665122d35f.png

如果有朋友知道其他更好实现这个需求的方案，可以私信，共享一下。