Date and Time when Table last queried

  • Hi,

    Is it possible to find out when a table was last accessed i.e Table has been selected from a query.

    Thanks

  • This script will look at sys.dm_db_index_usage_stats to determine the last reads/writes that occurred on a table; however, this DMV is reset when SQL is restarted, so be aware of that.

    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;

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • Often times this request is followed up by the OP stating they are trying to determine if they can safely delete some table(s). The excellent query posted does contain the warning about that being reset when sql restarts. This is very important when trying to determine if a certain table can be deleted. There may be a process that is only run once a year that looks at a certain table. This is sometime done at the end of the year and some services are restarted around that time too on some systems "just because". I would recommend that instead of dropping these tables you instead rename them to have a datetime that is about a year after they are renamed so you know when they have not been accessed for a period of time. Another option would be to archive those tables to a backup database somewhere.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What if the table does not have a index or primary key? I take it the above sql will only work if it has this?

  • J Good (6/10/2013)


    however, this DMV is reset when SQL is restarted, so be aware of that.

    When the database is closed. Hence when the SQL server service stops or any other time the database is closed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQL_Kills (6/10/2013)


    What if the table does not have a index or primary key? I take it the above sql will only work if it has this?

    Yes, the above query will still work - even on tables with no indexes of any kind.

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • J Good (6/10/2013)


    SQL_Kills (6/10/2013)


    What if the table does not have a index or primary key? I take it the above sql will only work if it has this?

    Yes, the above query will still work - even on tables with no indexes of any kind.

    Well technically speaking a table with no defined indexes has an index with a type of "HEAP" which is why your script will still work.

    create table HasIndexesTest

    (

    SomeID int,

    SomeValue varchar(10)

    )

    select * from sys.indexes

    where object_id = object_id('HasIndexesTest')

    If alter the table and add an index it will replace the HEAP index with the defined index.

    create clustered index IX_HasIndexesTest on HasIndexesTest(SomeID)

    select * from sys.indexes

    where object_id = object_id('HasIndexesTest')

    drop table HasIndexesTest

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (6/10/2013)


    Well technically speaking a table with no defined indexes has an index with a type of "HEAP" which is why your script will still work.

    You are correct. Thanks for providing some clarification.

    Twitter: @SQL_JGood
    Blog: sqljgood.wordpress.com/[/url]

  • A table with no clustered index is a heap. A heap can have non-clustered indexes on it and it remains a heap. Just clarifying.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply