Sql Server : When was the last time a table was updated

Quick one today:

Sometimes we need to find out the last time a table was touched, either for INSERT/UPDATE or for READ. This is not a regular use case.

In any given second, a busy database processes thousands of transactions; so it a rare scenario where we’ll stop the activity to find the time when a table was touched.

While it is rare, the need arises, mostly in DEV & TEST environments, where we need to query the last time a table was updated.

The below T-SQL helps us uncover the details:

--
-- Create temporary table for the test
--
CREATE TABLE dbo.TempUpdate (ID INT, Place VARCHAR(20))
GO

--
-- Perform INSERT and check the timestamp
--
INSERT INTO dbo.TempUpdate (ID, Place) VALUES (1, 'Test')
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'

 

INSERT Timestamp

--
-- Perform UPDATE and check the timestamp
--
UPDATE dbo.TempUpdate SET Place = 'Test2'
GO
SELECT OBJECT_NAME(object_id), last_user_update, last_user_lookup, last_user_scan, last_user_seek
FROM sys.dm_db_index_usage_stats
WHERE database_id = 23
AND OBJECT_NAME(object_id) = 'TempUpdate'
GO

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Updated Timestamp

Side Note: As notice, table scan was performed while performing UPDATE

--
-- Clean up
--
DROP TABLE dbo.TempUpdate
GO

Hope this helps,

_Sqltimes

Read Full Post »