Finding Last Date Time Updated for Any Table

In this post I am going to explain about how to know the last modified date time in the Sqlserver.for this letus create following table and insert the data in to the Sqlserver.

USE Practice
GO
CREATE TABLE Test
(ID INT,
COL VARCHAR(100))
GO
INSERT INTO Test
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO

Now we have created the table and run the any one query of 2 methods in the Sql server it will give the Last modified date of the table. 

Method 1 :

USE Practice

GO

Select name,modify_date from sys.objects where type_desc= 'USER_TABLE' and name='Test'

ORDER BY modify_date desc

Output :

Suppose If we want know all tables modified date then remove and condition in the above query.Then query will be as follows 

USE Practice

GO

Select name,modify_date from sys.objects where type_desc= 'USER_TABLE'

ORDER BY modify_date desc

Method 2 :

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'Practice')

AND OBJECT_ID=OBJECT_ID('test')

If we run the above query provides accurate details of when was  the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.

Output: