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.

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

USE [YOUR DATABASE]

SELECT
   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
GROUP BY
   tab.Name,
   par.rows
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.