Hello Willie,

you're right, the result from sp_spaceused might differ from the query, since the query itself doesn't add xml indexes or fulltext indexes for the given table, in contrast to sp_spaceused. I didn't need these so I left them away.

Anyway, I modified the query so you can query for a tablename and only one index in this table if desired. If I find some time, I'll add the query for fulltext and xml index space too.

BTW, the original query wasn't put together by me, but it's also a part of the sp_spaceused sys storeproc (sp_helptext 'sp_spaceused'), I just put it together for a quick view while performance tuning a database.

Regards

/*** Show index sizes for each index of a table ***/

SELECTo.name TableName, i.name IndexName, reserved_page_count, used_page_count,

CASE

WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

ELSE lob_used_page_count + row_overflow_used_page_count

END pages,

row_count,

LTRIM (STR ((CASE WHEN used_page_count >

(CASE

WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

ELSE lob_used_page_count + row_overflow_used_page_count

END)

THEN (used_page_count -

(CASE

WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

ELSE lob_used_page_count + row_overflow_used_page_count

END))

ELSE 0 END) * 8, 15, 0) + ' KB') IndexSize

FROM sys.dm_db_partition_stats s

INNER JOIN sys.objects o ON o.object_id = s.object_id

INNER JOIN sys.indexes i ON i.object_id = o.object_id and s.index_id = i.index_id

WHERE o.name = 'TableName'

--AND i.name = 'IndexName' -- use this to query size of one index in the table