From time to time, it’s important to examine the database objects in terms of table size (number of rows and storage) to avoid some performance issues on server.

How to Check Table Sizes in a SQL Database

If there are not many objects in the database, you can perform the check directly in the properties of individual tables under the Data space and index space fields. The size of the table is the sum of both values.

Table Sizes in a Database - Example

More conveniently and quickly, you can obtain statistics on the size of all tables in the database using the following script:


   tab.Name AS DB_Table,
   par.rows AS Table_RowCounts,
   CAST(ROUND((SUM(alloc.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Table_MB
FROM sys.tables tab
     INNER JOIN sys.indexes ind
        ON tab.OBJECT_ID = ind.object_id
     INNER JOIN sys.partitions par
        ON ind.object_id = par.OBJECT_ID
        AND ind.index_id = par.index_id
     INNER JOIN sys.allocation_units alloc
        ON par.partition_id = alloc.container_id
ORDER BY SUM(alloc.total_pages) DESC;

The result of the script will provide you with a list of all tables in the database, along with the number of rows in each table and the disk space they occupy in MB.

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

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