USE AdventureWorksDW2008R2 go SELECT object_name (si.[object_id]) AS [TableName], CASE WHEN si.[index_id] = 0 THEN 'Heap' WHEN si.[index_id] = 1 THEN 'CL' WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT ('00' + convert (VARCHAR, si.[index_id]), 3) ELSE '' END AS [IndexType], si.[name] AS [IndexName], si.[index_id] AS [IndexID], CASE WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD (m, -1, getdate ()) THEN '!! More than a month OLD !!' WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD (wk, -1, getdate ()) THEN '! Within the past month !' WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent' ELSE '' END AS [Warning], STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update] FROM sys.indexes AS si WHERE OBJECTPROPERTY (si.[object_id], 'IsUserTable') = 1 ORDER BY [Last Stats Update] DESC, [TableName], si.[index_id] -- -- Results -- TableName IndexType IndexName IndexID Warning Last Stats Update ProspectiveBuyer NC 002 IX_ProspectiveBuyer_ProspectAlternateKey 2 !! More than a month OLD !! 2012-03-29 13:43:47.647 FactSurveyResponse NC 004 IX_FactSurveyResponse_ProductSubcategoryKey 4 !! More than a month OLD !! 2012-03-29 13:43:47.640 FactSurveyResponse NC 003 IX_FactSurveyResponse_CustomerKey 3 !! More than a month OLD !! 2012-03-29 13:43:47.630 FactSurveyResponse NC 002 IX_FactSurveyResponse_DateKey 2 !! More than a month OLD !! 2012-03-29 13:43:47.623