查询最后修改表中数据的时间

如果表没有任何索引，则称为一个heap。对于这种表是无法查询最后数据修改时间的。除非使用第三方工具读取事务日志来确定。

对于有索引的表，则可以使用：

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test');

其中 user_updates的解释是：

The user_updates counter indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view. You can use this view to determine which indexes are used only lightly by your applications. You can also use the view to determine which indexes are incurring maintenance overhead. You may want to consider dropping indexes that incur maintenance overhead, but are not used for queries, or are only infrequently used for queries.

因此我们可以认为last_user_update是数据的最后修改时间。

官网链接：

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