最近開始研究 Database Maintenance 的方向
很多時候都想對公司的Table了解多一些
看看那個Database Table 佔的空間比較大

如果大家想看看在Database上不同的Table佔了多小空間..
可以在選擇了Database後執行以下的SQL Query

SELECT 
 t.NAME AS TableName,
 i.name AS IndexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
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
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID < 255 AND  
 i.index_id >= 1 
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

之後他便會出列出 以下的資料…其實都很有用的

IndexName = 在Table上有的 Index的名稱
RowCounts = 在這個Table上有多 行
最主要都是看 Total Space用了多小

之後的網誌會和大家分享怎樣可以 release返一些沒有使用的空間

Hope you find it useful