Two methods to find Table Size with Row Count in Database

In this article we will learn how can we find out the list tables with table size and number of rows inside the table. There are two ways we have listed in this article.

As a DBA you may face many circumstances when you come on the stage that you have to shrink the database or clean up database.

When it comes to cleaning up database you have to find out which tables are very big in size and consuming more space on data file.

It is important to know the number of rows, the table size, data space allocated as well as the index space used. There are multiple ways that you can get such information. Easy way is by finding from SSMS.

There are other functions or tables from where we can find such information.  Here in this article we will look at some queries that can be used to do this.

Table Size Using procedure : sp_spaceused

This method displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.


exec sp_spaceused 'TableName'


exec sp_spaceused 'dbo.CustomerMst'

This stored procedure accepts only single table name. If we wish to get list of all tables, we have to put some extra efforts to get list of all tables with space used.

Looping on all tables with sp_msforeachtable

Lets use sp_msforeachtable to loop over all the tables in the database and we can pass additional command to be executed along with table name. Here is the example.

SET @cmd = 'exec sp_spaceused ''?'''
EXEC sp_msforeachtable @cmd

sp_spaceused with sp_msforeachtable
Loop sp_spaceused with sp_msforeachtable

Wow, we can see Number of Rows, reserved Space, Used space in KB, index size in KB and Unused space in KB for each table. But there is some problem in this command. It generates different result-sets for each table. We have to find something else to get everything in single Result-set.

Get result in Temp Table which is generated by sp_spaceused command.

--Create a Temp Table named #TableSpace
CREATE TABLE #TableSpace (
	 TableName sysname
	,TotRows BIGINT
	,AllocatedSpace VARCHAR(50)
	,UsedSpace VARCHAR(50)
	,IndexSize VARCHAR(50)
	,UnusedSpace VARCHAR(50)

-- Loop through all tables using sp_msforeachtable and collect result into TempTable  #TableSpace
SET @str =  'exec sp_spaceused ''?'''
INSERT INTO #TableSpace 
EXEC sp_msforeachtable  @str

--Run Select Query on #TableSpaace
SELECT * FROM #TableSpace ORDER BY AllocatedSpace

-- Delete temp table #TableSpace 
drop table #TableSpace 
Load sp_msforeachtable in TempTable with sp_spaceused

Wonderful, we got the result in single Result-set. Let’s understand, what we did in the command above.

  • Create Temp Table with name – TableSpace
  • Collect result into TableSpace temp table by using sp_msforeachtable with sp_spaceused
  • Run select command on Temp table – TableSpace to get result.
  • Drop temp table

Please you can not use sp_msforeachtable on Database hosted on Azure server and you may encounter error given below

Could not find stored procedure 'sp_msforeachtable'.

In such situation another method given below works good.

Table Size Using allocation_units System Table

This is a method is which is mostly preferred by many experienced DBAs during their database analysis work.

In this SQL command we will use 4 system tables to generate final result. It is really interesting.

  • sys.tables : This table contains the list of system tables and user tables.
  • sys.indexes : This table contains the list of all the indexes generated on all the tables by user or system
  • sys.partitions : This table contains a row for each partition of all the tables and most types of indexes in the database.
  • sys.allocation_units : Contains a row for each allocation unit in the database.
    t.NAME AS TableName,p.rows TotalRows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    t.Name,  p.Rows
    TotalSpaceKB DESC, t.Name
Final Result by allocation_units
Final Result by allocation_units


If you are using SQL Server Management Studio (SSMS), instead of running a SQL query you can run a standard report by SSMS. 

  1. Right click on the database
  2. Navigate to Reports > Standard Reports > Disk Usage By Table

Are you running out of space? Are you planning to Shrink database file? Read this article before doing shrink database file

17 thoughts on “Two methods to find Table Size with Row Count in Database

  • Pingback: Delete Duplicate Rows from SQL Server Table - CodoBee

  • Pingback: Why we should not Shrink SQL Server database file? - CodoBee

  • December 10, 2020 at 9:31 pm

    Well I really enjoyed reading it. This post offered by you is very useful for correct planning. Giralda Hagan Zasuwa

  • December 15, 2020 at 12:45 am

    Well I sincerely liked reading it. This tip procured by you is very effective for accurate planning. Gypsy Johnnie Grae

  • December 17, 2020 at 1:38 pm

    I am actually glad to read this webpage posts which contains plenty of valuable data, thanks for providing these statistics. Courtnay Lesley Maite

  • December 18, 2020 at 2:49 am

    If you like to play games with to go through our process several times. Kerrin Karlis Ladew

  • December 18, 2020 at 9:35 am

    Appreciate you sharing, great blog article. Much thanks again. Really Great. Kassi Bealle Florette

  • December 18, 2020 at 1:12 pm

    You made a number of nice points there. I did a search on the subject and found most persons will have the same opinion with your blog. Mireielle Jethro Sheeree

  • December 18, 2020 at 5:22 pm

    Currently it looks like WordPress is the preferred blogging platform available right now. Hollie Skell Grew

  • December 18, 2020 at 8:20 pm

    Really enjoyed this blog post. Really looking forward to read more. Geralda Ancell Neron

  • December 18, 2020 at 10:31 pm

    If you like to organize running out of interested people. Fawne Dillon Winterbottom

  • December 19, 2020 at 1:35 pm

    You made some decent points there. I did a search on the topic and found most persons will go along with with your site. Justina Cary Phelgen

  • December 19, 2020 at 2:58 pm

    Our company are actually will promptly and also efficiently produce a warranty Premium renovation manhattan. Freddie Donalt Seessel

  • December 19, 2020 at 5:01 pm

    Fine way of describing, and good article to get data on the topic of my presentation topic, which i am going to present in academy. Nina Brendis Berthe

  • December 21, 2020 at 10:14 am

    You made some clear points there. I looked on the internet for the topic and found most people will consent with your blog. Perla Erhart Helbonnas

  • December 22, 2020 at 2:01 am

    Incredible points. Outstanding arguments. Keep up the good effort. Gaylene Arnaldo Wallis

  • December 23, 2020 at 8:48 am

    I like the valuable information you supply in your articles. Sapphira Eldridge Brodsky


Leave a Reply

Your email address will not be published. Required fields are marked *