October 14, 2013 at 10:55 pm
Comments posted to this topic are about the item Find the size of all Indexes in a database
October 15, 2013 at 3:54 pm
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"
October 15, 2013 at 9:22 pm
Thanks Perry for the feedback. Will try to rewrite the same as you advised
October 16, 2013 at 7:57 am
I prefer this
SELECTOBJECT_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"
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy