When Table Data Is Last Accessed And Modified in SQL Server

Here’s Quick script to find when table data is last modified or accessed

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

SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')

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

--Note: This will return null if sql server restarted and after that data is not accessed

           Demo:

/**** Create New Database ****/

Create Database Access

GO

use Access

GO

/**** Create Table ****/

Create Table test (id int , name varchar (100))

GO

/**** Insert New Record ****/

Insert into test values (1,'saurabh')

Insert into test values (2,'Sumit')

GO

/**** After insertion Check Stats ****/

SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*

FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')

Object_Name   last_user_update                  last_user_scan

test          2015-01-14 08:39:54.100           NULL

Here last user scan is null because we are just inserted data, If we read data then we will get read time also

So now we are going to read data and see if we get last user scan

Select * from access.dbo.test

GO

/**** Checking Stats ****/

SELECT OBJECT_NAME(OBJECT_ID) AS [Object_Name], last_user_update,last_user_scan  ,*

FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'Access') AND OBJECT_ID=OBJECT_ID('test')

So here we just updated table , didnt read

Similarly if we want to see how current indexes are performing on table we can see user scan , user seek or look ups