From campisano.org
Jump to navigation Jump to search

System query

Show all tables sizes

SELECT s.Name AS SchemaName, t.NAME AS TableName, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
 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
WHERE t.type_desc = 'USER_TABLE' 
GROUP BY t.Name, s.Name, p.rows
ORDER BY TotalSpaceMB DESC, t.Name