Comments posted to this topic are about the item Find the size of all Indexes in a database

Calling the index physical stats dmv in a cursor could have serious ramifications on a large database, you should bear this in mind.

Query sys.partitions and sys.allocation_units for details of index allocated and used space.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)

Thanks Perry for the feedback. Will try to rewrite the same as you advised

I prefer this

SELECT         OBJECT_NAME(i.object_id) AS TableName
         , ISNULL(i.name, 'HEAP') AS IndexName
         , i.index_id as IndexID
         , i.type_desc AS IndexType
         , p.partition_number AS PartitionNo
         , p.rows AS NumRows
         , au.type_desc AS InType
         , au.total_pages AS NumPages
         , au.total_pages * 8 AS TotKBs
         , au.used_pages * 8 AS UsedKBs
         , au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p 
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE 
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) NOT LIKE 'sys%' 
AND OBJECT_NAME(i.object_id) NOT LIKE 'queue%'
AND object_name(i.object_id) <> 'dtproperties'
ORDER BY p.rows desc

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)