查詢目前資料庫內，每一個資料表的磁碟空間使用資訊 -- Displaying disk space information about eyvery table

雖然我們可以使用 sp_spaceused 來顯示指定資料表及其索引的磁碟空間資訊。但是僅能對單一資料表來執行。
筆者這邊利用[動態管理檢視]來查詢目前資料庫內，每一個資料表的使用空間資訊，請參考以下的範例：

--查詢目前資料庫內，每一個資料表的使用空間資訊
SELECT a3.name AS N'結構描述',  a2.name AS N'資料表',  a1.rows AS N'資料列筆數',  (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)',  a1.data * 8.0/1024 AS '資料(MB)',  
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)',  
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)'  
FROM  (SELECT   ps.object_id, SUM ( CASE  WHEN (ps.index_id < 2) THEN row_count  ELSE 0 END  ) AS [rows],  
SUM (ps.reserved_page_count) AS reserved, SUM (  CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)  
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)  END ) AS data,  
SUM (ps.used_page_count) AS used  
FROM sys.dm_db_partition_stats ps  
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id,  SUM(ps.reserved_page_count) AS reserved,  SUM(ps.used_page_count) AS used  
FROM sys.dm_db_partition_stats ps  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)  
WHERE it.internal_type IN (202,204)  
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)  
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )   
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)  
WHERE a2.type <> N'S' and a2.type <> N'IT'  
ORDER BY a3.name, a2.name

請參考下圖所示：