SQLServerPedia

Today in one of the Online Communities, a question was asked. How to find the size/space used details of an Index in SQL Server 2000?

The places to look for this information are the sysindexes and sysobjects tables. The  values in the reserved and used columns are in pages. Number of pages * 8 KB will give the actual size. The below query gives the Reserved and Used details for all the indexes in a table.

select
object_name(a.id),
a.name,sum(reserved)*8 as [sum_reserved (KB)],
sum(used)*8 as [sum_used (KB)]
from
sysindexes a, sysobjects b
where
indid in (1,255) and a.id = b.id and b.type='U'
group by
a.name,a.id
order by object_name(a.id), a.name

Even though this is not the most elegant way of getting the information, this query provides the requisite information.