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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools