How to check the size of your SQL Server Database tables

If you're running your web applications on a shared server, then you know all too well that often times there is a limit on the storage space for your database. So it is crucial to keep an eye on your storage so that you can avoid any serious downtime in the future.

Lucky for us, SQL Server makes it relatively easy to query for the size of each table by querying against various system views, which I will break down  below.

But first, here is the query in its entirety.

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.name, s.name, p.rows
ORDER BY 
    TotalSpaceMB DESC;

Explanation of the query

Let's break down the views being queried and joined.

sys.tables - This view contains a row for every single table in your given database, along with any pertinent information related to that table. Note that it will only display tables that you have permission to read.

sys.indexes - This view contains a row for every index in your database. It's important to note that indexes can vastly improve performance when querying data, however they do come at a storage cost.

sys.partitions - This view contains a row for every partition of a table or an index. Which is essentially information on the storage and structure of a particular table and index.

sys.allocations - This view provides information about the storage allocation units used by database objects, such as tables and indexes.

sys.schemas - And lastly, this view contains a row for every schema in the database. Once again, assuming you have permission to view that particular schema.

Taking a look at the output

TableName - The system name given to the database table

SchemaName - The schema that the table belongs to

RowCounts - The total number of rows in the table

TotalSpaceMB - The total space allocated the table in megabytes.

UsedSpaceMB - The total space taken up by the table in megabytes.

UnusedSpaceMB - The space that is allocated, but not yet used by the table.

Frequent monitoring of your database tables is also a great way to measure how active certain parts of your applications are. You might launch a new feature only to realize that it is quickly growing in popularity and quickly requiring more storage space.