Display the size of all tables in Sql Server 2005

February 14, 2008

Display the size of all tables in Sql Server 2005

Thursday, February 14, 2008 Posted by Suprotim Agarwal
Labels: SQL Server Administration

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

Did you like this post?



	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter


Feedback:

comments

5 Responses to "Display the size of all tables in Sql Server 2005"

Jeff Benedict said...

May 4, 2009 7:55 AM

Works great except for tables that are in a different schema. So join the sys.Schemas to get the qualified name and then you'll get all tables.. here's my modification
note: i also created another Table Var to store the results and then display them afterwards...

DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @TableSizes AS TABLE
([TblName] VARCHAR(255), [NumRows] INT,
[Reserved_Size] VARCHAR(10), [Data_Size] VARCHAR(10), [Index_Size] VARCHAR(10), [Used] VARCHAR(10))

DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT s.NAME +'.'+t.name FROM sys.Tables t
JOIN sys.Schemas s ON s.SCHEMA_ID = t.schema_id
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TableSizes
EXEC sp_spaceused @str
SET @I = @I +1
END

SELECT * FROM @TableSizes
Suprotim Agarwal said...

May 6, 2009 11:22 AM

Thanks Jeff!
Simon Trundle said...

April 1, 2010 4:49 AM

Thanks for this - one issue I found was when running this against databases with very large tables (>10million), you would not get the results back due to the @TableSizes temporary table column widths being too small.

So i've made the temp table column widths bigger and also added a default sort order for the results.

Here's the updated version.

Thanks again.

/*

Script to show the sizes of the tables in the specfied database

*/
USE yourdbname

DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @TableSizes AS TABLE
([TblName] VARCHAR(255), [NumRows] INT,
[Reserved_Size] VARCHAR(20), [Data_Size] VARCHAR(20), [Index_Size] VARCHAR(20), [Used] VARCHAR(20))

DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT s.NAME +'.'+t.name FROM sys.Tables t
JOIN sys.Schemas s ON s.SCHEMA_ID = t.schema_id
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TableSizes
EXEC sp_spaceused @str
SET @I = @I +1
END

SELECT * FROM @TableSizes ORDER BY NumRows DESC
Suprotim Agarwal said...

April 2, 2010 8:17 AM

Thanks Simon for modifying the script to suit larger tables!
Suresh said...

September 8, 2010 11:19 AM

if you get the error 'convert ..'
for @TableSizes change it to [NumRows] VARCHAR(30) and get the result set in an excel and sort it..:)