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:
- Login to SSMS.
- Right click the database.
- 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