How big?
It isn’t very often that you need to understand which tables take up most space within your database. Normally, knowing how much space the entire database consumes is enough.
However, SQL Server is expensive if you use a cloud provider. Even the smallest database is expensive.
Knowing which tables take up the most space can be crucial in those instances. For example, suppose you store infrequently accessed data in the database, such as historical information. In that case, you might decide that this can be moved from the database to something more appropriate, such as Table Storage.
Determining where those bytes are.
As with much in SQL Server, this information is freely available if you know where to look. The SQL code below shows where your bytes are: –
SELECT
t.NAME AS TableName,
i.name AS indexName,
SUM(p.rows) AS RowCounts,
SUM(a.total_pages) AS TotalPages,
SUM(a.used_pages) AS UsedPages,
SUM(a.data_pages) AS DataPages,
(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
(SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
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
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
TotalSpaceMB desc
