--查詢對象（包含所擁有的所有索引）所占空間
select 
    max(obj.name) as name,
    max(p.rows) as TotalRows,
    SUM(a.total_pages)*8 as [SpaceUsed(KB)]
from sys.objects obj
    join sys.indexes i on obj.object_id = i.object_id
    join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id 
    join sys.allocation_units a on p.partition_id = a.container_id
where obj.type = 'U'
group by obj.object_id
order by SUM(a.total_pages) desc

exec sp_spaceused 't_ws_tranlist'    --查詢單個表

--SELECT
--TableName = obj.name,
--TotalRows = prt.rows,
--[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
--FROM sys.objects obj
--JOIN sys.indexes idx on obj.object_id = idx.object_id
--JOIN sys.partitions prt on obj.object_id = prt.object_id
--JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
--WHERE obj.type = 'U' AND idx.index_id IN (0, 1)
--GROUP BY obj.name, prt.rows
--ORDER BY SUM(alloc.used_pages) DESC