Display the size of all tables in Sql Server 2005

sp_spaceused returns number of rows, disk space reserved, and disk space used by a table. However when you have to return the space used by all the tables in a database, you have two options: One is to loop through all the tables and then pass the table name to the sp_spaceused procedure. The second is to use the undocumented sp_MSforeachtable procedure. We will explore both of these over here:

The Lengthy Way

USE yourdbname
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
EXEC sp_spaceused @str
SET @I = @I +1
END

 

Note: The advantage in taking the lengthy approach is that you can create another temporary table and sort the tables based on the space used.

The Short Way

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

Note: sp_MSforeachtable is an undocumented stored procedure

References :
http://msdn2.microsoft.com/en-us/library/ms188776.aspx
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic37975.aspx
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm