Tuesday, February 8, 2011

How to get index and table sizes in a database

select * from sys.partitions where object_id=276964113

select * from sys.allocation_units where container_id in
(select partition_id from sys.partitions where object_id=276964113)

select
i.[object_id],
i.index_id,
p.partition_number,
p.rows as [#Records],
a.total_pages * 8 as [Reserved(kb)],
a.used_pages * 8 as [Used(kb)]
from
sys.indexes as i
inner join
sys.partitions as p
on i.object_id = p.object_id
and i.index_id = p.index_id
inner join
sys.allocation_units as a
on p.partition_id = a.container_id
where
i.[object_id] = 276964113
order by
p.partition_number
go

2 comments:

  1. The above works in 2005 and above and not in 2000.
    I am still exploring how to do this in 2000

    ReplyDelete
  2. in 2000 you can get using this.

    sp_MSIndexSpace

    ReplyDelete