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.
[…] There are many ways to check the size of a table in SQL Server which I’ve posted about here. […]