Get a last access time for tables in SQL Server

How to get a last access time for tables in SQL Server ?

To get the last time when table was accessed in SQL Server, you can use SQL Server dynamic management view sys.dm_db_index_usage_stats, which returns counts of different types of index operations and the time each type of operation was last performed.

DMV sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.

Following statement, uses DMV sys.dm_db_index_usage_stats, which returns last acccessed times for all the tables in database SQLServerTutorials.

 

SELECT OBJECT_NAME(object_id) AS [Table] ,
Last_user_update, Last_user_seek, Last_user_scan, Last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SQLServerTutorials')
GO

Following fields are returned by above statement :

Table is a table name belongs to a specifc database.

Last_user_update is a time of last user update.

Last_user_seek is a time of last user seek.

last_user_lookup is a time of last user lookup.

If you want to see the last access time for specific table then you can modify above statement as given below.

Following statement returns the last accessed time for table salesDetails.

SELECT OBJECT_NAME(object_id) AS [Table] ,
Last_user_update, Last_user_seek, Last_user_scan, Last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SQLServerTutorials')
AND OBJECT_NAME(object_id) = 'SalesDetails'

GO

SQL Server Interview Questions

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 31,132 total views,  7 views today