Just a while ago I replied to one question where the user wanted to know how to find out the date on which the statistics for a given index were updated. The requirement was to reindex the tables based on the last update date of the statistics. The best approach is to rebuild the indexes based on the average fragmentation. But at times we may have to build the indexes based on the statistics update date. I have an instance in my environment where the client wants all the indexes to be rebuilt irrespective of the fragmentation level. Hence the answer to this question was very similar to what I had already implemented.
DBCC SHOW_STATISTICS provides the details of an index. Here is an example of this query in action.
DBCC SHOW_STATISTICS ('backupset', backupsetuuid)
The output is as follows.
Whereas STATS_DATE can be used to get the last updated date of the indexes on a given database. This is the one I make use of always.
For SQL Server 2000:
SELECT B.Name,A.Name as IndexName, STATS_DATE ( a.id , indid ) as StatsDate FROM sysindexes A, sysobjects B WHERE a.id=b.id
For SQL Server 2005 & above:
SELECT object_name(object_id)as TableName, name as IndexName, STATS_DATE(object_id, stats_id)as StatsDate FROM sys.stats ORDER BY TableName, IndexName
The output will be something like this