SQL SERVER – List Tables with Size and Row Counts

What defines a table as a huge table, is it row count or size of the table? Well, during Comprehensive Database Performance Health Check I often want to know both the database which I am tuning. Today we will see a very simple script that lists table names with the size of the table and along with that row counts.

SQL SERVER - List Tables with Size and Row Counts rowcounts-800x291

Run the following script in your SSMS.

SELECT
t.NAME AS TableName,
MAX(p.rows) AS RowCounts,
(SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB,
(SUM(a.data_pages) * 8) /1024.0 as DataSpaceMB
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
WHERE i.OBJECT_ID > 255
AND i.index_id IN (0,1)
GROUP BY t.NAME
ORDER BY TotalSpaceMB DESC

I hope you find this short but effective script to find Size and Row Counts helpful. Here are few other blog posts which you may find interesting.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
MySQL MariaDB – Query Using Temp Table
Next Post
Forwarded Records and Performance – SQL in Sixty Seconds #155

Related Posts

4 Comments. Leave new

  • There is a minor rounding bug in above script due to integer division:

    SELECT
    t.NAME AS TableName,
    SUM(p.rows) AS RowCounts,
    (SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB,
    (SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB,
    (SUM(a.data_pages) * 8) /1024.0 as DataSpaceMB
    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
    WHERE i.OBJECT_ID > 255
    AND i.index_id IN (0,1)
    GROUP BY t.NAME
    ORDER BY TotalSpaceMB DESC

    should fix it. In my case I was seeing several tables with row counts > 0 but a 0 space utilization.

    Reply
    • Thanks Jason, I reflected your changes in the script and will write a separate blog post giving credit to you.

      Reply
  • The counted rows in this script are wrong when you have a table with multiple partitions.
    To give you the correct value for rows in a table, please use “MAX(P.[rows])” instead of “SUM(P.[rows])”.

    Reply

Leave a Reply

Menu