Davide Mauri Blog

Experiences with SQL Server

SQL Server 2005 Indexes: How many and how big?

This little snip of code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are:

select
    [name],
    type_desc,
    space_used_in_kb = (page_count * 8.0),
    space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I
inner join
   
sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]

 

Legacy Comments


sunil
2006-01-10
re: SQL Server 2005 Indexes: How many and how big?
i need information about indexs

sunil
2006-01-10
re: SQL Server 2005 Indexes: How many and how big?
i need information about indexs

sunil
2006-01-10
re: SQL Server 2005 Indexes: How many and how big?
i need information about indexs

sunil
2006-01-10
re: SQL Server 2005 Indexes: How many and how big?
i want information about indexs