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