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.

Syntax

exec sp_spaceused 'TableName'

Example

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.

DECLARE @cmd VARCHAR(max)
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
DECLARE @str VARCHAR(500)
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.
SELECT 
    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
GROUP BY 
    t.Name,  p.Rows
ORDER BY 
    TotalSpaceKB DESC, t.Name
Final Result by allocation_units
Final Result by allocation_units

Bonus

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
    Permalink

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

    Reply
  • December 15, 2020 at 12:45 am
    Permalink

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

    Reply
  • December 17, 2020 at 1:38 pm
    Permalink

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

    Reply
  • December 18, 2020 at 2:49 am
    Permalink

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

    Reply
  • December 18, 2020 at 9:35 am
    Permalink

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

    Reply
  • December 18, 2020 at 1:12 pm
    Permalink

    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

    Reply
  • December 18, 2020 at 5:22 pm
    Permalink

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

    Reply
  • December 18, 2020 at 8:20 pm
    Permalink

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

    Reply
  • December 18, 2020 at 10:31 pm
    Permalink

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

    Reply
  • December 19, 2020 at 1:35 pm
    Permalink

    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

    Reply
  • December 19, 2020 at 2:58 pm
    Permalink

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

    Reply
  • December 19, 2020 at 5:01 pm
    Permalink

    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

    Reply
  • December 21, 2020 at 10:14 am
    Permalink

    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

    Reply
  • December 22, 2020 at 2:01 am
    Permalink

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

    Reply
  • December 23, 2020 at 8:48 am
    Permalink

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

    Reply

Leave a Reply

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