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

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

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT *
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

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

USE AdventureWorks;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH 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 LastServerRestart

FROM

(

  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

  dbname

ORDER 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_update

FROM sys.databases D

LEFT OUTER JOIN sys.dm_db_index_usage_stats S on D.database_id = S.database_id

WHERE D.database_id > 4

 AND S.object_id > 100

GROUP by D.name, D.database_id

)

SELECT * from STATS

UNION

SELECT D.name, D.database_id, NULL, NULL, NULL, NULL

 FROM sys.databases D

WHERE D.database_id > 4

  AND not exists (select * from STATS where name = D.name)

ORDER by 1