Find Size Of Tables In Database – SQL Server

When we are working with SQL Server databases, very often we will come across the problem of abnormal database size growth. In such a situation we need to get the size of tables in the database to identify the abnormal sized tables to figure out the cause of growth.

Here we will go through some of the easiest methods to get the size of all the tables in a database. This will help you to identify the tables with abnormal size.

1. Find Size Of Tables Using SSMS Standard Report

The easiest way to find the size of all the tables in a database is to use the SQL Server Management Studio’s (SSMS) standard report called Disk Usage by Table. To access the disk usage table:

1. Login to SSMS.
2. Right click the database.
3. In the right-click menu go to Reports >> Standard Reports >> Disk Usage by Tables.

This standard report will give you the number of records in the table, reserved physical space in KB and it’s breakup.

2. Using SP_SPACEUSED

This is another way to find the size of all the tables in a database and order them. we can use the SP_SPACEUSED system stored procedure along with the SP_MSForEachTable system stored procedure. As SP_MSForEachTable returns a result set for each table, we will use a temp table to insert the result set and finally return the table sizes from the temp table.

Create TABLE #TableSize (TableName VARCHAR(200),Rows VARCHAR(20),      
       Reserved VARCHAR(20),Data VARCHAR(20),index_size VARCHAR(20), 
       Unused VARCHAR(20))
exec sp_MSForEachTable 'Insert Into #TableSize Exec sp_spaceused [?]'
Select 
	TableName, CAST(Rows AS bigint) As Rows, 
	CONVERT(bigint,left(Reserved,len(reserved)-3)) As Size_In_KB 
	from #TableSize order by 3 desc
Drop Table #TableSize

3. Using SYS.TABLES & SYS.Allocation_UNITS

Another method is to use sys.tables, sys.partitions and sys.allocation_units system tables.

SELECT 
    t.NAME AS TableName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.partitions p ON t.object_id = p.OBJECT_ID
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY 
    t.Name
ORDER BY 
    TotalSpaceKB Desc

Related

• Simple methods to find the table creation date and time.

Reference

• Stackeoverflow