February 26, 2008 at 10:30 am
Hi,
Is there a way to find when a table was last reindexed (DBCC DBREINDEX)
Don't want to post this in SQL 2005 forum, but if this can't be done in 2000 can this information be found in 2005 environment.
Thanks,
R
February 26, 2008 at 11:11 am
This works good both on SQL Server 2000 and also SQL Server 2005.
SELECT object_name(si.[id]) AS [TableName]
, rowcnt AS [Row Count]
, CASE
WHEN si.indid = 0 then 'Heap'
WHEN si.indid = 1 then 'CL'
WHEN INDEXPROPERTY (si.[id], si.[name], 'IsAutoStatistics')
= 1 THEN 'Stats-Auto'
WHEN INDEXPROPERTY (si.[id], si.[name], 'IsHypothetical')
= 1 THEN 'Stats-HIND'
WHEN INDEXPROPERTY (si.[id], si.[name], 'IsStatistics')
= 1 THEN 'Stats-User'
WHEN si.indid between 2 and 250 then 'NC '
+ RIGHT('00' + convert(varchar, si.indid), 3)
ELSE 'Text/Image'
END AS [IndexType]
, si.[name] AS IndexName, si.indid
, CASE
WHEN si.indid BETWEEN 1 AND 250
AND STATS_DATE (si.[id], si.indid)
< DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.indid BETWEEN 1 AND 250
AND STATS_DATE (si.[id], si.indid)
< DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.indid BETWEEN 1 AND 250
THEN 'Stats recent'
ELSE ''
END AS [Warning]
, STATS_DATE (si.[id], si.indid) AS [Last Stats Update]
FROM sysindexes AS si
WHERE OBJECTPROPERTY(id, 'IsUserTable') = 1
-- and si.indid = 1
-- and STATS_DATE (si.[id], si.indid) is not null
ORDER BY [TableName], si.[indid]
SQL DBA.
February 26, 2008 at 11:32 am
Purrrrfect, thanks Sanjay.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply