When Table Is Last Updated And Modified In SQL Server

Since SQL 2005 its easy to find when your table is last accessed , when modified and when last data is modified in SQL Server.

Just a quick demo here:

Use Tempdb

GO

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

GO

Insert into emp values (1 , 'saurabh')

GO

select * from emp

GO

/**When table data is last accessed and modified:**/

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( 'tempdb')

AND OBJECT_ID=OBJECT_ID('emp')

/**When table structure is last Modified**/

SELECT name, [modify_date], [Create_date]  FROM sys.tables where name = 'emp'