Last Activity - SQL Server Objects

The Dynamic Management View (DMV) sys.dm_db_index_usage_stats can be used to find out the counts of different types of index operations and the time each type of operation was last performed. This view counts every individual seek, scan, lookup or update on the specified object caused by user-submitted queires or by internally generated queries, such as scan for gathering statistics. The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

Permissions : Requires VIEW SERVER STATE permission.

USE [Test]
GO

/* Cretate Test Table */
CREATE TABLE SampleTest
(   
    Id    INT,
    Val VARCHAR(100)
)
GO

/* Insert data into test table - This will refect in DMV counter */
INSERT INTO SampleTest
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
GO

/* Query to Find out Last Activity on SQL Objects (such as Tables, Views) */
SELECT     OBJECT_NAME(OBJECT_ID) AS ObjectName
        ,DB_NAME(DATABASE_ID) AS DatabaseName
        ,last_user_update
        ,*
FROM    sys.dm_db_index_usage_stats
WHERE    DATABASE_ID = DB_ID( 'Test')
        AND OBJECT_ID = OBJECT_ID('SampleTest')