How can I retrieve the size used for each index in a table? 1

[susre]

I need to retrieve the size used of each index used in a table. How can i do? sp_spaceused is not useful, because i need not the sum, but the size of each index.
Thank you very much,

 

[ColinM]

This may give you a rough idea by returning the rows

select name,rows from sysindexes

but the rows are different from sizes, i.e. a multi-column index must have a bigger size but could have the same rows.

 

[susre]

This query can be more useful, the only problem is that i got a -8 result Index_Size_In_KB so for sure there is something wrong, what do you think?

DECLARE @pagesize int
select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'

SELECT o.name TableName, i.name IndexexesName, i.dpages*@pagesize Data_Size_In_KB,(i.used - i.dpages)*@pagesize Index_Size_In_KB, i.reserved*@pagesize Reserved_Size_In_KB
FROM sysobjects o, sysindexes i
WHERE o.id = i.id AND i.indid > 2 AND i.indid < 255 AND o.type = 'U'
ORDER BY o.name

 

[ColinM]

Must be in around this bit surely

i.used - i.dpages

something aint right, as I ran it and it says I got a few 4 gig indexes on a 300mb database!

 

[susre]

You are right, it should be certanly this value:
(i.used - i.dpages)
Do you know how can I correct the query?

 

[ColinM]

Actually looking a bit more closely at the results, it is the statistics that are causing problems
If you add this:

AND i.Name not like '_WA_Sys%'

to get rid of the statistics, which are the ones giving the negative values.