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
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.