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 [TableName], si.[index_id]
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 [TableName], si.[index_id]
No comments:
Post a Comment