Archive for the ‘Most Recent Updates for Tables’ Category

How to find the most recent updates for a table?

You wish to find the most recent updates for a table or for all tables in a SQL Server database, you wanted to know if the data are being updated at all. How can you do it as quickly as possible? Yes. There’s a Dynamic Management View for that and it is: sys.dm_db_index_usage_stats.

 

Here’s an example of how you can use it and verify it:

  1. Create a test table.
  2. Insert some data into the test table.
  3. Query the date and time the data is inserted.
  4. Query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table.
  5. Update the table by modifying data.
  6. Query the date and time the data is updated.
  7. Query the sys.dm_db_index_usage_stats DMV to confirm the most recent update to the table.

Example Code:

USE AdventureWorks2012
GO
-- create a test table
CREATE TABLE VeryTalentedPeople
(
ID INT identity (1,1)
, FIRSTNAME VARCHAR(100)
, LASTNAME VARCHAR(100)
)
GO

-- insert some data into the table
INSERT INTO VeryTalentedPeople
VALUES ('Barak','Obama')
, ('Ellie', 'Goulding')
, ('Katy', 'Perry')
, ('Lady','Gaga')
GO

-- query the date and time the data is inserted
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

-- update the table by modifying data
UPDATE VeryTalentedPeople
SET FIRSTNAME = 'Stefani'
, LASTNAME = 'Germanotta'
WHERE FIRSTNAME = 'Lady'
and LASTNAME = 'Gaga'

-- query the date and time the data is updated
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

REFERENCES:

sys.dm_db_index_usage_stats
http://msdn.microsoft.com/en-us/library/ms188755.aspx