SQL Server Table Size Report


By:   |   Updated: 2020-03-19   |   Comments (1)   |   Related: More > Monitoring

Problem

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

Solution

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
AS
BEGIN
   SET NOCOUNT OFF;
 
   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 
   SELECT *
   FROM @StorageRepTable
   ORDER BY Table_Name;
 
   SET NOCOUNT ON;
END
GO

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
GO

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


Last Updated: 2020-03-19


get scripts

next tip button



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.

View all my tips
Related Resources





Comments For This Article




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

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



download


Recommended Reading

How to setup SQL Server alerts and email operator notifications

Reading the SQL Server log files using TSQL

SQL Server Backup Monitoring with PowerShell

Determining space used for all tables in a SQL Server database

Methods to determine the status of a SQL Server database





get free sql tips
agree to terms


Learn more about SQL Server tools