SQL Server Table Size Report

By:   |   Comments (2)   |   Related: > Monitoring


I have a requirement to generate a storage report for all user tables in a SQL Server database.  How can I do this?


My solution involves creating a T-SQL stored procedure in a SQL Server application's database, called dbo.USP_DetailedTablesStorageReport that will generate the needed storage information on all user tables in a user database.

The procedures use a table variable called @StorageRepTable in order to store the reports results.

The procedure executes the system stored procedure sp_msforeachtable that executes the system stored procedure sp_spaceUsed (as its parameter it uses '?' as the replacing character) for each user table.

The procedure's output is a table that consists of the following information (columns) for each user table:

  • Table_Name - Name of the object for which space usage information was requested.
  • RowCnt - Number of rows existing in the table.
  • TableSize - Total amount of reserved space for the table.
  • DataSpaceUsed - Total amount of space used by data in the table.
  • IndexSpaceUsed - Total amount of space used by indexes in the table.
  • Unused_Space - Total amount of space reserved for the table, but not yet used.

All this data is inserted into the table variable and presented in the final query. After the procedure finishes the table variable is freed automatically from SQL Server's memory.

Here is the T-SQL code for the stored procedure:

-- =================================================================================
-- Author:         Eli Leiba
-- Create date:    2020-02
-- Procedure Name: dbo.USP_DetailedTablesStorageReport
-- Description:    Generates storage report for all tables in database 
-- ==================================================================================
CREATE PROCEDURE dbo.USP_DetailedTablesStorageReport
   DECLARE @SQLstring VARCHAR (300);
   --Create a Temporary Table to store report
   DECLARE @StorageRepTable TABLE (
      [Table_Name] VARCHAR (80)
      ,RowCnt INT
      ,TableSize VARCHAR(80)
      ,DataSpaceUsed VARCHAR(80)
      ,IndexSpaceUsed VARCHAR(80)
      ,Unused_Space VARCHAR(80)
   --Create the Dynamic TSQL String
   SET @SQLstring = 'sp_msforeachtable ''sp_spaceused "?"''';
   --Populate Temporary Report Table
   INSERT INTO @StorageRepTable
   EXEC (@SQLstring);
   -- Sorting the report result 
   FROM @StorageRepTable
   ORDER BY Table_Name;

Example Execution

Generate a detailed storage report for all tables in the pubs database (first create and compile the procedure in pubs database):

EXEC dbo.USP_DetailedTablesStorageReport

And the results are as follows:

query results
Next Steps
  • You can create and compile this simple procedure in your application database and use it as a simple SQL tool for generating a detailed storage report on all of your user tables.
  • Try to fine tune this to allow different sort orders as well as different sizes such as MB, GB, etc.
  • Check out this related tip Determining space used for all tables in a SQL Server database

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, August 12, 2020 - 8:16:21 AM - Nastaran Back To Top (86289)
YES. It's a great tip, well written, thank you for posting this!

Saturday, March 21, 2020 - 3:10:11 PM - Yoni Back To Top (85157)

Great tip, well written, thank you for posting this!

get free sql tips
agree to terms