database_table_size_report.sql
· 613 B · MySQL
Ham
USE [你的資料庫名稱];
SELECT
s.name AS SchemaName,
t.name AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSizeKB,
SUM(a.used_pages) * 8 AS UsedSizeKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSizeKB
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.partitions p ON t.object_id = p.object_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
p.index_id IN (0,1) -- 0:堆積表, 1:群集索引
GROUP BY
s.name, t.name, p.rows
ORDER BY
TotalSizeKB DESC;
| 1 | USE [你的資料庫名稱]; |
| 2 | SELECT |
| 3 | s.name AS SchemaName, |
| 4 | t.name AS TableName, |
| 5 | p.rows AS RowCounts, |
| 6 | SUM(a.total_pages) * 8 AS TotalSizeKB, |
| 7 | SUM(a.used_pages) * 8 AS UsedSizeKB, |
| 8 | (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSizeKB |
| 9 | FROM |
| 10 | sys.tables t |
| 11 | INNER JOIN |
| 12 | sys.schemas s ON t.schema_id = s.schema_id |
| 13 | INNER JOIN |
| 14 | sys.partitions p ON t.object_id = p.object_id |
| 15 | INNER JOIN |
| 16 | sys.allocation_units a ON p.partition_id = a.container_id |
| 17 | WHERE |
| 18 | p.index_id IN (0,1) -- 0:堆積表, 1:群集索引 |
| 19 | GROUP BY |
| 20 | s.name, t.name, p.rows |
| 21 | ORDER BY |
| 22 | TotalSizeKB DESC; |
| 23 |