SQL Server中统计哪些表被访问过？

在 SQL Server 里，若要了解哪些表被访问过，可借助以下几种方式：

1. 使用 SQL Server 扩展事件（Extended Events）

SQL Server 扩展事件是一个轻量级的高性能事件跟踪系统，能用来捕获和分析数据库中的各种活动。你可以创建一个扩展事件会话，对表访问事件进行跟踪。

示例代码

-- 创建一个扩展事件会话
CREATE EVENT SESSION [TrackTableAccess] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.username)
    WHERE ([object_name] IS NOT NULL)
)
ADD TARGET package0.event_file(SET filename=N'TrackTableAccess.xel',max_file_size=(5),max_rollover_files = 4)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

-- 启动扩展事件会话
ALTER EVENT SESSION [TrackTableAccess]
ON SERVER
STATE = START;

代码解释

• 首先创建了一个名为 TrackTableAccess 的扩展事件会话，用于捕获 sqlserver.sql_statement_completed 事件。
• WHERE ([object_name] IS NOT NULL) 过滤条件保证只捕获涉及表访问的事件。
• 把捕获到的事件存储在 TrackTableAccess.xel 文件中。
• 最后启动该扩展事件会话。

查看捕获结果

-- 查看捕获的事件
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'varchar(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'varchar(255)') AS client_hostname,
    event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(255)') AS username,
    event_data.value('(event/data[@name="object_name"]/value)[1]', 'varchar(255)') AS table_name
FROM 
    (SELECT CAST(event_data AS XML) AS event_data 
     FROM sys.fn_xe_file_target_read_file('TrackTableAccess*.xel', NULL, NULL, NULL)) AS events;

2. 利用系统视图

SQL Server 提供了一些系统视图，能够查看表的使用统计信息。

示例代码

-- 查看表的使用统计信息
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM 
    sys.dm_db_index_usage_stats s
JOIN 
    sys.objects o ON s.object_id = o.object_id
WHERE 
    s.database_id = DB_ID()
    AND o.type = 'U';

代码解释

• sys.dm_db_index_usage_stats 视图存储了表和索引的使用统计信息。
• user_seeks、user_scans、user_lookups 和 user_updates 列分别表示用户发起的索引查找、全表扫描、索引查找和更新操作的次数。
• 通过与 sys.objects 视图连接，能够获取表的名称。

3. 借助 SQL Server Profiler（不推荐用于生产环境）

SQL Server Profiler 是一个图形化工具，可用于捕获和分析 SQL Server 中的各种事件。不过，该工具会对性能产生较大影响，不建议在生产环境中使用。

使用步骤

1. 打开 SQL Server Profiler。
2. 新建一个跟踪，选择要捕获的事件类别，例如 Object:Created、Object:Deleted、Object:Altered 等。
3. 启动跟踪，SQL Server Profiler 会开始捕获和记录事件。
4. 分析捕获到的事件，找出被访问的表。