How to Check Table Sizes in SQL Server

There are many ways to check the size of a table in SQL Server. The way you will do it will likely depend on what your task at hand is.

For example, a quick look at the Disk Usage by Top Tables report on a database you’ve never seen before would instantly show you the large tables in a database. But if you want this information saved somewhere else, e.g. to a table in SQL, it’s an easy task for us to run a TSQL script and output to Excel or CSV.

In this post, I’m sharing a variety of methods to check table sizes in MS SQL, including:
# SSMS Disk Usage by Top Tables Report
# Check Table Properties in SSMS
# MSSQL sp_spaceused System Stored Proc
# MSSQL Script to Get Table Sizes

SSMS Disk Usage by Top Tables Report

To open this report:-
1. Open SQL Server Management Studio (SSMS).
2. Expand Databases.
3. Right-click the Database and select Reports > Standard Reports > Disk Usage by Top Tables

The report shows that this database has 2 tables close to 700MB in size, both containing the same number of rows but there are some differences in the indexes.

I find this method one of the most efficient methods for getting the disk usage of tables in MSSQL.

Check Table Properties in SSMS

Another way to check the sizes of tables in SQL Server Management Studio (SSMS) is to open the Table Properties within the Object Explorer.

To do this, expand the database and tables. You may need to add a filter to find your table.

Now, right-click the table and select Properties as shown in the screenshot below.

Open the Storage tab on the left-hand sidebar. It may take a second for the window to load, but this will show the index space, data space usage and the table row count.

MSSQL sp_spaceused System Stored Proc

For this and the following methods to get SQL table sizes, we need to open a new query window.

We can run the sp_spaceused MS SQL system stored procedure, adding the Schema and Table names as parameters.

-- Check Table Sizes (MS SQL System SP)
sp_spaceused '[dbo].[FactResellerSalesXL_CCI]';

MSSQL Script to Get Table Sizes

Lastly, we can run a query on SQL Server system tables to get the table sizes.

-- Get Table Size SQL Server Script
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) 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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

That’s us done for now. For more SQL Tips from a random MS SQL DBA in the field, feel free to check out my SQL DBA Blog page which includes a list of my latest posts.

SQL DBA Blog