How to know the space used by each table in SQL SERVER and generate summary report

I want to know the following things.

How many rows are present in each table in a database
What is the index size of each table
What is the data size of each table.
Total how many rows are present in all the tables in a database.
Total hom much space is used by data and index by all tables in a database.

In SQL Server sp_spaceused stored procedure is used to know the size and rows count for a single table. If you want to know the summary of all tables you need to use a cursor to loop all the tables.

We can use sp_MSforeachtable stored procedure to execute a command. But this will give you resultset for each execution.

Combined these two commands in the following way you can get a summary of each table in the current database.

This will help us in understanding Production data sizes and plan for the capacity planning.


/*Declare temp table to hold the data*/
DECLARE @t TABLE(Name NVARCHAR(MAX),
Rows BIGINT,
Reserved NVARCHAR(MAX),
Data NVARCHAR(MAX),
Index_Size NVARCHAR(MAX),
Unused NVARCHAR(MAX))

/*Insert the data output from sp_spaceused command to temp table */
insert into @t
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

/*Display the raw data for all the tables*/
select * from @t order by name

/*Remove the 'KB' present in the data*/
update @t
Set reserved = RTRIM((REPLACE(reserved,'KB','')))
,data = RTRIM((REPLACE(DATA,'KB','')))
,index_size = RTRIM((REPLACE(index_size,'KB','')))
,unused = RTRIM((REPLACE(unused,'KB','')))

/* Get the total of all tables data to know the summary*/
SELECT 'Total' AS TOTAL
, SUM(rows) AS 'Rows'
, SUM(CONVERT(BIGINT, reserved)) AS 'Reserved (KB)'
, SUM(CONVERT(BIGINT, data)) AS 'Data (KB)'
, SUM(CONVERT(BIGINT, index_size)) AS 'Index Size (KB)'
, SUM(CONVERT(BIGINT, unused)) AS 'Unused (KB)'
FROM @t