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
at

5 comments:

  1. Jeff BenedictMay 4, 2009 at 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

    ReplyDelete
  2. Suprotim AgarwalMay 6, 2009 at 11:22 AM

    Thanks Jeff!

    ReplyDelete
  3. Simon TrundleApril 1, 2010 at 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

    ReplyDelete
  4. Suprotim AgarwalApril 2, 2010 at 8:17 AM

    Thanks Simon for modifying the script to suit larger tables!

    ReplyDelete
  5. UnknownSeptember 8, 2010 at 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..:)

    ReplyDelete