In SQL Server 2008 is there a way to generate a report as to how much space on disk each table takes?

Question

IN SQL Server 2008 is there a way to generate a report as to how much space on disk each table takes?

I can do it one by one by checking the storage properties, but I was wondering if there was a way, maybe even a query to generate such a report.

Answer 1

Yes, use the sp_spaceused stored procedure to get this information...

USE AdventureWorks;
GO
EXEC sp_spaceused N'Purchasing.Vendor';
GO

Result will look like this:

name    rows    reserved    data    index_size  unused
Vendor  104         48 KB   16 KB   32 KB   0 KB

To get the stats for all the tables in the DB use:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Answer 2

in addition to @kzen's correct answer, to get a single result set that is sortable use this:

declare @t table (name sysname,rows int,reserved nvarchar(40),data nvarchar(50),index_size nvarchar(50),unused nvarchar(50))
INSERT INTO @T
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

--order by reserved space
select * from @t
order by CONVERT(int,LEFT(reserved,CHARINDEX(' ',reserved))) DESC