timmy revised this gist 10 months ago. Go to revision
No changes
timmy revised this gist 10 months ago. Go to revision
No changes
timmy revised this gist 10 months ago. Go to revision
No changes
timmy revised this gist 11 months ago. Go to revision
1 file changed, 22 insertions
database_table_size_report.sql(file created)
| @@ -0,0 +1,22 @@ | |||
| 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; | |
Newer
Older