SQL Server Table Size Report
By: Eli Leiba | Updated: 2020-03-19 | Comments (1) | Related: More > 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 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
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:
- 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
About the author
View all my tips