How to Find Your Table Sizes in MS SQL

All databases come with a fixed size, which can be increased at any time by purchasing more space. However you might just need to manage your database and clear out old records. This article shows you how to determine each table size in MS SQL using SSMS.

You will need to do this via Microsoft SQL Server Management Studio (SSMS), which if you do not have installed can be found here. You will also need to open up the firewall to allow you to connect to the SQL Server, this is shown here.

Follow these steps to find out your table sizes.

1. Open Microsoft SQL Server Management Studio and connect to your database. Details of the IP address and username/password can be found in your 'Space Summary'.
2. Click on the 'New Query' button. Using F5 or the 'Execute' button to run each script.
3. You can view your table sizes within your database by using the following script snippet:

   
   SELECT   
       t.NAME AS TableName,
       s.Name AS SchemaName,
       p.rows AS RowCounts,
       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.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 SUM(a.total_pages) * 8 DESC
   

Return to Category