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