Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

The query:

SELECT name AS TableName, create_date AS CreatedDate, modify_date as ModifyDate FROM sys.tables order by ModifyDate

will tell me the last time a table was created and modified (from a DDL perspective). But I want to know the last time actual data was either inserted or removed from the table. Is it possible to get this in SQL Server?

share|improve this question

1 Answer

up vote 3 down vote accepted

You might be able to get an idea from

SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE object_id=object_id('T')

but the data there is not persisted across service restarts and might not be accurate (e.g. running DELETE FROM T WHERE 1=0 will update the time even though no rows were actually deleted)

share|improve this answer
Thanks. This is returning back nothing. Any reason why that might be besides a restart? – dublintech Feb 13 '12 at 10:09
2  
You did change the WHERE condition presumably? And you have VIEW SERVER STATE permissions? Also it will only return rows for those objects that are currently in the metadata cache. – Martin Smith Feb 13 '12 at 10:11
Sorry my where condition was wrong. You are correct. Thanks. – dublintech Feb 13 '12 at 10:46
wheen i did a SELECT * FROM sys.dm_db_index_usage_stats I noticed that all the object_ID are numbers. THere are no table names so this will not work for me. Any suggestions? – JuanVelez Mar 26 at 12:36
1  
@JuanVelez - You need to join onto other views such as sys.tables or use the object_name function. – Martin Smith Mar 26 at 13:12
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.