Search

Wednesday, May 9, 2012

When were Statistics last updated on all indexes


SELECT o.name, i.name AS [Index Name], 

       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],

       s.auto_created, s.no_recompute, s.user_created

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON o.[object_id] = i.[object_id]

INNER JOIN sys.stats AS s WITH (NOLOCK)

ON i.[object_id] = s.[object_id]

AND i.index_id = s.stats_id

WHERE o.[type] = 'U'

ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);     

No comments:

Post a Comment