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
