in

SG SQL Server Usergroup

The Premier web platform for Microsoft SQL Server professionals in Singapore

Do you want to know when was the last time your tables were accessed ?

Last post 06-26-2008 1:17 PM by pethiraj. 0 replies.
Page 1 of 1 (1 items)
Sort Posts: Previous Next
  • 06-26-2008 1:17 PM

    • pethiraj
    • Top 10 Contributor
    • Joined on 02-26-2008
    • Singapore
    • Posts 8

    Do you want to know when was the last time your tables were accessed ?

    Please run the following query to know that

    WITH LastTableAccess ([object_id], readDate, writeDate) AS(

    SELECT [object_id],last_user_seek,NULL FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()

    UNION ALL

    SELECT [object_id],last_user_scan,NULL FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()

    UNION ALL

    SELECT [object_id],last_user_lookup,NULL FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()

    UNION ALL

    SELECT [object_id],last_user_lookup,last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()

    )

     

    SELECT s.name AS SchemaName,st.name AS TableName,MAX(readDate) AS LastRead,MAX(writeDate) AS LastWrite

    FROM sys.tables AS st

    INNER JOIN sys.schemas AS s ON s.[schema_id] = st.[schema_id]

    LEFT JOIN LastTableAccess AS ta ON ta.[object_id] = st.[object_id]

    GROUP BY st.name,s.name

     

Page 1 of 1 (1 items)
Powered by Community Server (Commercial Edition), by Telligent Systems