Skip to content
Home ยป Get a last access time for tables in SQL Server

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 31,132 total views,  7 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.