sp_spaceused rewritten as one query (no cursors)
I reverse engineered sp_spaceused and packed the exact same logic into a query that isn't limited to running on a single object at a time.
* Query that simulates running sp_spaceused on every applicable object in a database and gathering it all into a single result set
* This set-based approach is more efficient then actually doing that.
* The logic is derived strait from the source of sp_spaceused, so the numerical values should be a 1-to-1 match.
* Three changes have been made to the result set:
* (1) The object's schema and type are included.
* (2) Actual numbers are used in the result instead of strings with ' KB' appended to the end.
* (3) The reserved, data, index_size, and unused columns are renamed with a postfix of '_kb'.
* Compatibility: 2005+
* Released by Greg Drake on 2013-06-03
'schema' = schema_name(so.schema_id)
,'name' = so.name
,'type' = so.type
,'type_desc' = so.type_desc
,'rows' = partition_stats.row_count
,'reserved_kb' = (calc.reserved_page_count * 8)
,'data_kb' = (partition_stats.page_count * 8)
,'index_size_kb' = (CASE WHEN (calc.used_page_count > partition_stats.page_count) THEN (calc.used_page_count - partition_stats.page_count) ELSE 0 END * 8)
,'unused_kb' = (CASE WHEN calc.reserved_page_count > calc.used_page_count THEN (calc.reserved_page_count - calc.used_page_count) ELSE 0 END * 8)
'object_id' = sddps.[object_id]
,'row_count' = sum(
WHEN (sddps.index_id < 2) THEN sddps.row_count
,'page_count' = sum(
WHEN (sddps.index_id < 2) THEN (sddps.in_row_data_page_count + sddps.lob_used_page_count + sddps.row_overflow_used_page_count)
ELSE sddps.lob_used_page_count + sddps.row_overflow_used_page_count
,'used_page_count' = sum(sddps.used_page_count)
,'reserved_page_count' = sum(sddps.reserved_page_count)
) partition_stats ON (so.[object_id] = partition_stats.[object_id])
LEFT OUTER JOIN
,'used_page_count' = sum(sddps2.used_page_count)
,'reserved_page_count' = sum(sddps2.reserved_page_count)
sys.dm_db_partition_stats sddps2 ON (sit.[object_id] = sddps2.[object_id])
sit.internal_type IN (202,204,211,212,213,214,215,216)
) summary_data ON (so.[object_id] = summary_data.parent_object_id)
'reserved_page_count' = (partition_stats.reserved_page_count + isnull(summary_data.reserved_page_count, 0))
,'used_page_count' = (partition_stats.used_page_count + isnull(summary_data.used_page_count, 0))
so.[type] IN ('U ','V ','S ','SQ','IT')