When were the statistics updated?

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.

STATS_DATE and DBCC SHOW_STATISTICS provide the date of the most recent update for statistics on a table.

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:

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:

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