SQL Server Table Size Report

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:

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