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
> The sp_spaceused Stored Procedure
> SQL Script to Get Table Sizes

SSMS 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 However, the last way of getting this data is really the better way.

Check Table Properties in SSMS

Another way to check the sizes of tables in SQL Server Management Studio is to open Table Properties within the SSMS 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.

The sp_spaceused Stored Procedure

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

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]';

SQL 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

If you read previously in this post that this way, a SQL script is the better way of getting this info out of all these examples. I say this as a Database Administrator. We need all the metadata on growth, and usually when we need some information we’re running a quick query.

Hope this helps!