Refer to:
Index Related Dynamic Management Views and Functions (Transact-SQL)
at:
http://technet.microsoft.com/en-us/library/ms187974.aspx
This code works in 2005, but I have not tested in 2008 ... give it a try if it does you have some of what you asked for. If it does / does not work, please post and then my knowledge will increase.
SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name',
Case Indid
WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
End 'Type',
'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS
'# Rows inserted deleted or updated', --, o.type
i.keys
FROM sysobjects o, sysindexes i
WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255)Gets date of last statistics update number of rows added, deleted or updated since last update
Same caveat as above, but this will also report on Auto Created Statistic
SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid) AS Date_Updated
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
WHERE xtype = 'U' AND i.name IS NOT NULL
ORDER BY o.name ASC,i.name ASC
Again tested on 2005 needs to be tested on 2008. Again asking you to report back if it does / does not work in 2008
This will tell you the date and a lot more than what you would want to know about indexes.
SELECT o.name as 'Table', i.name as 'Index', c.name as 'Column',
'Index Type' =
CASE
WHEN PATINDEX('%_wa_sys_%',i.name) = 0
THEN 'Index'
ELSE 'Statisical'
END,
'Primary' =
CASE
WHEN (i.status & 0x800)= 0
THEN 'No'
ELSE 'Yes'
END,
'Clustered' =
CASE WHEN (i.status & 0x10)= 0
THEN 'No'
ELSE 'Yes'
END,
'Unique' =
CASE WHEN (i.status & 0x2) = 0
THEN 'No'
ELSE 'Yes'
END,
'Ignore Dup Key' =
CASE WHEN (i.status & 0x1) = 0
THEN 'No'
ELSE 'Yes'
END,
'Ignore Dup Row' =
CASE WHEN (i.status & 0x4)= 0
THEN 'No'
ELSE 'Yes'
END,
'No Recompute' =
CASE WHEN (i.status & 0x1000000) = 0
THEN 'No'
ELSE 'Yes'
END,
'Computed' =
CASE WHEN (c.iscomputed) = 0
THEN 'No'
ELSE 'Yes'
END,
'Nullable' =
CASE WHEN (c.isnullable) = 0
THEN 'No'
ELSE 'Yes'
END,
i.OrigFillFactor AS 'Orig Fill Factor', i.rowcnt as 'Est.RowCount', i.reserved * cast(8 as bigint) as ReservedKB,
i.used * cast(8 as bigint) as UsedKB, t.name as 'Column Type',
'Precision' =
Case c.xprec WHEN 0
THEN ' '
ELSE CAST(c.xprec as VARCHAR(3))
END,
'Scale' =
Case c.xscale
WHEN 0
THEN ' '
ELSE CAST(c.xscale as VARCHAR(3))
END,
c.Length as 'Length', 'Updated' = STATS_DATE(i.id,i.indid)
from sysobjects o with(nolock) inner join sysindexes i with(nolock) on o.id = i.id
inner join sysindexkeys k with(nolock) on i.id = k.id and I.indid = K.indid
inner join syscolumns c with(nolock) on k.id = c.id and K.colid = c.colid
inner join systypes t with(nolock) on c.xtype = t.xtype where o.xtype <> 'S' -- Ignore system objects
Order By o.name, i.name