Joseph Michael Pesch
VP Programming

SQL Server Query to Gather Individual Table Space Used

by 22. October 2012 16:29

 

select

  tablename = o.name

, totalpages = sum(a.total_pages)

, usedpages = sum(a.used_pages)

, pages = sum(case when a.type <> 1 then a.used_pages

when p.index_id < 2 then a.data_pages else 0 end)

, SUM(a.used_pages)*8096 AS 'Size(B)'

, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)

from sys.objects o

join sys.partitions p on p.object_id = o.object_id

join sys.allocation_units a on p.partition_id = a.container_id

where o.type = 'U'

group by o.name

order by usedpages desc

 

Tags:

SQL Server

Comments are closed