| Can you write down a query to get size of all tables in SQL Server?
Below is the query to get all table size of length with the help below query here we used sys.tables, sys.indexes, sys.partitions, sys.allocation_units, sys.schemas some sys tables that will helpw to get data.
SELECT
sys_s.Name AS Schema_Name,
sys_t.Name AS Table_Name,
sys_p.rows AS Row_Counts,
CAST(ROUND((SUM(sys_a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Consume_MB,
CAST(ROUND((SUM(sys_a.total_pages) - SUM(sys_a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(sys_a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables sys_t
INNER JOIN sys.indexes sys_i ON sys_t.OBJECT_ID = sys_i.object_id
INNER JOIN sys.partitions sys_p ON sys_i.object_id = sys_p.OBJECT_ID AND sys_i.index_id = sys_p.index_id
INNER JOIN sys.allocation_units sys_a ON sys_p.partition_id = sys_a.container_id
INNER JOIN sys.schemas sys_s ON sys_t.schema_id = sys_s.schema_id
GROUP BY sys_t.Name, sys_s.Name, sys_p.Rows
ORDER BY sys_s.Name, sys_t.Name
GO
| | |