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
Enter your email address to subscribe to this blog and receive notifications of new posts by email.