怎么判断某个库中的某张表在某个时间段有没有被使用过

今天逛论坛，遇到一个经常问的问题，怎么判断某个库中的某张表在某个时间段有没有被使用过？记得以前在Aaron Bertrand的Blog上看到过，去翻一翻，找到了转过来.When was my database / table last accessed?
里面提到了几种方法，列举如下：
1.使用SQL Server审计功能，通过审计功能的记录看判断对应的数据库对象的最后访问时间。

USE master;GOCREATE SERVER AUDIT Test_Server_Audit    TO FILE ( FILEPATH = 'C:\Audits\' );GOALTER SERVER AUDIT Test_Server_Audit    WITH (STATE = ON);GOUSE AdventureWorks;GOCREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit    FOR SERVER AUDIT Test_Server_Audit    ADD (SELECT ON Person.Address BY PUBLIC)    WITH (STATE = ON);GOSELECT *    FROM Person.Address;GOSELECT *    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);GOUSE AdventureWorks;GOALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit    WITH (STATE = OFF);GODROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;GOUSE master;GOALTER SERVER AUDIT Test_Server_Audit    WITH (STATE = OFF);GODROP SERVER AUDIT Test_Server_Audit;GO

2.2008以后，可以使用查询动态视图sys.dm_db_index_usage_stats来实现。

USE AdventureWorks;GOSET ANSI_WARNINGS OFF;SET NOCOUNT ON;GOWITH agg AS(    SELECT        [object_id],        last_user_seek,        last_user_scan,        last_user_lookup,        last_user_update    FROM        sys.dm_db_index_usage_stats    WHERE        database_id = DB_ID())SELECT    [Schema] = OBJECT_SCHEMA_NAME([object_id]),    [Table_Or_View] = OBJECT_NAME([object_id]),    last_read = MAX(last_read),    last_write = MAX(last_write)FROM(    SELECT [object_id], last_user_seek, NULL FROM agg    UNION ALL    SELECT [object_id], last_user_scan, NULL FROM agg    UNION ALL    SELECT [object_id], last_user_lookup, NULL FROM agg    UNION ALL    SELECT [object_id], NULL, last_user_update FROM agg) AS x ([object_id], last_read, last_write)GROUP BY    OBJECT_SCHEMA_NAME([object_id]),    OBJECT_NAME([object_id])ORDER BY 1,2;

3.通过SQL Agent创建对应的JOB或者数据快照，记录数据库中数据表的访问时间，然后通过查询这张表来实现对应的功能。

以上第一种方法，配置起来相当复杂一些，但是可以准确的记录数据库的操作，并且对一些非法操作可以起到预防作用。
第二种方法，需要指出的是，只能查询最后一次重启数据库后的数据，也就是说，数据库重启以后，该动态视图sys.dm_db_index_usage_stats里面的信息会被清空，所以查询出来的信息可能不准确。
第三种方法，需要额外创建JOB或者快照来实现，但是可以记录整个数据库的数据表访问变化。
三种方法各有利弊，在实际工作中，可以根据自己的情况来设定。

在文章的最后Q/A环节中，还提到了一些数据库的访问脚本：
例如：
1.去除系统对象的脚本

;WITH agg AS(  SELECT      max(last_user_seek) last_user_seek,      max(last_user_scan) last_user_scan,      max(last_user_lookup) last_user_lookup,      max(last_user_update) last_user_update,      sd.name dbname  FROM      sys.dm_db_index_usage_stats i      JOIN  master..sysdatabases sd on database_id = sd.dbid      where  i.object_id > (select max(object_id) from sys.objects)       group by sd.name)SELECT  dbname,  last_read = MAX(last_read),  last_write = MAX(last_write),(select create_date from sys.databases where name='tempdb') as LastServerRestartFROM(  SELECT dbname, last_user_seek, NULL FROM agg  UNION ALL  SELECT dbname, last_user_scan, NULL FROM agg  UNION ALL  SELECT dbname, last_user_lookup, NULL FROM agg  UNION ALL  SELECT dbname, NULL, last_user_update FROM agg) AS x (dbname, last_read, last_write)GROUP BY  dbnameORDER BY 2;

2.去除系统数据库和系统对象的查询数据库和数据库用户对象的脚本

; WITH STATS as (SELECT D.name, D.database_id, max(last_user_seek) as last_user_seek, max(last_user_scan) as last_user_scan, max(last_user_lookup) as last_user_lookup, max(last_user_update) as last_user_updateFROM sys.databases DLEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_idWHERE D.database_id > 4 AND S.object_id > 100GROUP by D.name, D.database_id)SELECT * from STATSUNIONSELECT D.name, D.database_id, NULL, NULL, NULL, NULL FROM sys.databases DWHERE D.database_id > 4  AND not exists (select * from STATS where name = D.name)ORDER by 1