To view the amount of space occupied by all tables in SQL Server

2010-01-26

sp_spaceused can view the space occupied by a table, but cannot view all the tables at once. Today, we studied this SP and wrote the following query:

--Refresh system data
DBCC UPDATEUSAGE (0) with NO_INFOMSGS

--8k per page
Select name = Name,id,
rows = Convert (char (one), rows),
reserved = LTrim (str (reservedpages * 8.,15,0) + ' KB '),
data = LTrim (str (pages * 8.,15,0) + ' KB '),
index_size = LTrim (str ((usedpages-pages) * 8.,15,0) + ' KB '),
unused = LTrim (str ((reservedpages-usedpages) * 8.,15,0) + ' KB ')
From
(Select Name,id,
reservedpages = 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
),
rows = SUM (
Case
When (p.index_id < 2) and (A.type = 1) then P.rows
Else 0
END
)
From Sys.partitions p, sys.allocation_units a,sysobjects o
where p.partition_id = a.container_id and p.object_id = O.id
GROUP BY Name,id
) A
ORDER BY reservedpages Desc

To view the amount of space occupied by all tables in SQL Server