Tuesday 2 June 2009

Table Statistics query

When were my table statistics last updated?

SQL 2005+ Script

-- sql 2005+
SELECT DISTINCT
  OBJECT_NAME(object_id) AS TableName
, [name] AS IndexName
, STATS_DATE(object_id, index_id) AS TableStatsUpdated
FROM sys.indexes WITH (NOLOCK)
ORDER BY STATS_DATE(object_id, index_id) DESC;

SQL 2000 Script

-- sql 2000
SELECT DISTINCT
  OBJECT_NAME(id) AS TableName
, [name] AS IndexName
, STATS_DATE(id, indid) AS TableStatsUpdated
FROM sysindexes WITH (NOLOCK)
ORDER BY STATS_DATE(id, indid) DESC;

No comments: