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


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

5 comments:

Jeff Benedict said...

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...

Thanks Jeff!

Simon Trundle said...

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...

Thanks Simon for modifying the script to suit larger tables!

Suresh said...

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..:)