Checking the size of the tables is one of the duties that DBAs frequently perform. In order to do capacity planning, it is imperative that a parameter be utilized to monitor database expansion.
The size of each table in the database is determined by the following popular query, which is available over the internet. You will be provided with the number of rows in each table, the overall amount of space allotted and spent, and other information.
SELECT
t.name AS TableName,
s.name AS SchemaName,
p.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 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
GROUP BY
t.name, s.name, p.rows
ORDER BY
TotalSpaceMB DESC, t.name
Happy learning!!
Big Data & SQL 