Cloud Training (Online)




SQL Server 101 – Get the size of all tables in database

You might have a scenario where you want to find out how much space that each of the tables in your database is taking so that you can take action on it.

There are various ways which one could use to get the size of the database. Below is one such option which includes a query.

How to get the size of all the tables in the database in SQL Server ?

SELECT 
    systable.NAME AS TableName,
    s.Name AS SchemaName,
    partitions.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables systable
INNER JOIN      
    sys.indexes as sysindex ON systable.OBJECT_ID = sysindex.object_id
INNER JOIN 
    sys.partitions as partitions ON sysindex.object_id = partitions.OBJECT_ID AND sysindex.index_id = partitions.index_id
INNER JOIN 
    sys.allocation_units a ON partitions.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON systable.schema_id = s.schema_id
WHERE 
    systable.NAME NOT LIKE 'dt%' 
    AND systable.is_ms_shipped = 0
    AND systable.OBJECT_ID > 255 
GROUP BY 
    systable.Name, s.Name, partitions.Rows