DMV Queries - Get Date/Time Table Last Accessed

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown/ChillyDBA
Date:        10 may 2010/26 Jun 2012
                26 Jun 2013 - ChillyDBA - added an SP version that takes @DatabaseName as parameter

Description

This table valued function accepts an optional <TableName> parameter and returns the last Seek, Scan and Lookup accesses for one or many tables.  It also returns the last access time for the table, which is the greater of the 3 values
 
 

Code

Function:

DROP FUNCTION udf_GetLastTableAccessTime
GO

CREATE FUNCTION udf_GetLastTableAccessTime (@TableName SYSNAME)
RETURNS @TableLastAccessed TABLE
  
(
      
TableName           SYSNAME,
      
LastUserSeek        DATETIME,
      
LastUserScan        DATETIME,
      
LastUserLookup      DATETIME,
      
TablelastAccessed   DATETIME
  
)
AS

/****************************************************************
Purpose:   To return the last access times for one or many
           tables in a single database.
           Scan, Seek and Lookup access will be retrieved, and
           also the most recent time of the 3
          
Author:        Unknown
History:   10 May 2010
           26 Jun 2012 - ChillyDBA - converted to a table valued function  
               and added the seek, scan and lookup subcategories

*****************************************************************/

BEGIN
  
;WITH LastActivity (ObjectID, LastUserSeek, LastUserScan, LastUserLookup, TablelastAccessed) AS
  
(
      
SELECT
          
object_id           AS ObjectID,
          
last_user_seek      AS LastUserSeek,
           NULL                
AS LastUserScan,
           NULL                
AS LastUserLookup,
          
last_user_seek      AS TablelastAccessed
      
FROM sys.dm_db_index_usage_stats u (NOLOCK)
      
WHERE database_id = db_id(db_name())

      
UNION

       SELECT
          
object_id           AS ObjectID,
           NULL                
AS LastUserSeek,
          
last_user_scan      AS LastUserScan,
           NULL                
AS LastUserLookup,
          
last_user_scan      AS TablelastAccessed
      
FROM sys.dm_db_index_usage_stats u(NOLOCK)
      
WHERE database_id = db_id(db_name())

      
UNION

       SELECT
          
object_id           AS ObjectID,
           NULL                
AS LastUserSeek,
           NULL                
AS LastUserScan,
          
last_user_lookup    AS LastUserLookup,
          
last_user_lookup    AS TablelastAccessed
      
FROM sys.dm_db_index_usage_stats u(NOLOCK)
      
WHERE database_id = db_id(db_name())
   )

  
INSERT @TableLastAccessed
  
(
      
TableName,
      
LastUserSeek,
      
LastUserScan,
      
LastUserLookup,
      
TablelastAccessed
  
)
  
SELECT
      
OBJECT_NAME(so.object_id)   AS TableName,
      
MAX(la.LastUserSeek)        AS LastUserSeek,
      
MAX(la.LastUserScan)        AS LastUserScan,
      
MAX(la.LastUserLookup)      AS LastUserLookup,
      
MAX(la.TablelastAccessed)   AS TablelastAccessed
  
FROM sys.objects so
  
LEFT JOIN LastActivity la (NOLOCK) ON so.object_id = la.ObjectID
  
WHERE so.type = 'U'
  
AND so.object_id > 100
  
AND OBJECT_NAME(so.object_id) = ISNULL(@TableName, OBJECT_NAME(so.object_id))
  
GROUP BY OBJECT_NAME(so.object_id)
  
ORDER BY OBJECT_NAME(so.object_id)
  
  
RETURN

END


Test Code:

USE AdventureworksDW
GO

-- look at the results for all tables
SELECT * FROM udf_GetLastTableAccessTime(NULL)

-- perform a scan against DimCustomer, wait for 2 seconds, then perfom a seek
SELECT * FROM DimCustomer
WAITFOR delay '00:00:02'
SELECT * FROM DimCustomer WHERE customerkey = 11001

-- look at the results for DimCustomer - the seek and scan should show up, with the
--seek being the most recent access time
SELECT * FROM udf_GetLastTableAccessTime('DimCustomer')


 
 

Stored Procedure:


/****** Object:  UserDefinedFunction [dbo].[udf_GetLastTableAccessTime]    Script Date: 06/26/2013 20:47:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_GetLastTableAccessTime]
                                      
@DatabaseName   SYSNAME,
                                      
@TableName      SYSNAME
AS

/****************************************************************
Purpose:   To return the last access times for one or many
           tables in a single database.
           Scan, Seek and Lookup access will be retrieved, and
           also the most recent time of the 3
          
Author:        Unknown
History:   10 May 2010
           26 Jun 2012 - ChillyDBA - converted to a table valued function  
               and added the seek, scan and lookup subcategories
           26 Jun 2013 - ChillyDBA - converted to an SP in order that it can
               be stored centrally and invoked for any DB

*****************************************************************/

BEGIN
   DECLARE    
@Cmd    VARCHAR(MAX)
  
SELECT      @Cmd    = ''

  
SELECT @Cmd= @Cmd
  
+ CHAR(13) + CHAR(10) + 'USE ' + @DatabaseName
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + ';WITH LastActivity (ObjectID, LastUserSeek, LastUserScan, LastUserLookup, TablelastAccessed) AS'
  
+ CHAR(13) + CHAR(10) + '('
  
+ CHAR(13) + CHAR(10) + ' SELECT'
  
+ CHAR(13) + CHAR(10) + ' object_id AS ObjectID, '
  
+ CHAR(13) + CHAR(10) + ' last_user_seek AS LastUserSeek,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserScan,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserLookup,'
  
+ CHAR(13) + CHAR(10) + ' last_user_seek AS TablelastAccessed'
  
+ CHAR(13) + CHAR(10) + ' FROM sys.dm_db_index_usage_stats u (NOLOCK)'
  
+ CHAR(13) + CHAR(10) + ' WHERE database_id = db_id(db_name())'
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + ' UNION'
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + ' SELECT'
  
+ CHAR(13) + CHAR(10) + ' object_id AS ObjectID,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserSeek,'
  
+ CHAR(13) + CHAR(10) + ' last_user_scan AS LastUserScan,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserLookup,'
  
+ CHAR(13) + CHAR(10) + ' last_user_scan AS TablelastAccessed'
  
+ CHAR(13) + CHAR(10) + ' FROM sys.dm_db_index_usage_stats u(NOLOCK)'
  
+ CHAR(13) + CHAR(10) + ' WHERE database_id = db_id(db_name())'
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + ' UNION'
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + ' SELECT'
  
+ CHAR(13) + CHAR(10) + ' object_id AS ObjectID,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserSeek,'
  
+ CHAR(13) + CHAR(10) + ' NULL AS LastUserScan,'
  
+ CHAR(13) + CHAR(10) + ' last_user_lookup AS LastUserLookup,'
  
+ CHAR(13) + CHAR(10) + ' last_user_lookup AS TablelastAccessed'
  
+ CHAR(13) + CHAR(10) + ' FROM sys.dm_db_index_usage_stats u(NOLOCK)'
  
+ CHAR(13) + CHAR(10) + ' WHERE database_id = db_id(db_name())'
  
+ CHAR(13) + CHAR(10) + ')'
  
+ CHAR(13) + CHAR(10)
   +
CHAR(13) + CHAR(10) + 'SELECT'
  
+ CHAR(13) + CHAR(10) + '   ''' + @DatabaseName + ''' AS DatabaseName,'
  
+ CHAR(13) + CHAR(10) + ' OBJECT_NAME(so.object_id) AS TableName,'
  
+ CHAR(13) + CHAR(10) + ' MAX(la.LastUserSeek) AS LastUserSeek,'
  
+ CHAR(13) + CHAR(10) + ' MAX(la.LastUserScan) AS LastUserScan,'
  
+ CHAR(13) + CHAR(10) + ' MAX(la.LastUserLookup) AS LastUserLookup,'
  
+ CHAR(13) + CHAR(10) + ' MAX(la.TablelastAccessed) AS TablelastAccessed'
  
+ CHAR(13) + CHAR(10) + 'FROM sys.objects so'
  
+ CHAR(13) + CHAR(10) + 'LEFT JOIN LastActivity la (NOLOCK) ON so.object_id = la.ObjectID'
  
+ CHAR(13) + CHAR(10) + 'WHERE so.type = ''U'''
  
+ CHAR(13) + CHAR(10) + 'AND so.object_id > 100'
  
+ CHAR(13) + CHAR(10) + 'AND OBJECT_NAME(so.object_id) = ' + ISNULL(@TableName, 'OBJECT_NAME(so.object_id)')
   +
CHAR(13) + CHAR(10) + 'GROUP BY OBJECT_NAME(so.object_id)'
  
+ CHAR(13) + CHAR(10) + 'ORDER BY OBJECT_NAME(so.object_id)'
  
+ CHAR(13) + CHAR(10)

  
EXEC (@Cmd)
  
END


 

Test Code:

-- look at the results for all tables


EXEC [dbo].[usp_GetLastTableAccessTime] 'Adventureworks', NULL

 
ċ
udf_GetLastTableAccessTime.sql
(3k)
Andy Hughes,
Jun 26, 2012, 7:28 AM
ċ
usp_GetLastTableAccessTime.sql
(4k)
Andy Hughes,
Jun 26, 2013, 7:19 PM
Comments