DBA 需快速掌握各資料表佔用磁碟空間與列數,找出大表進行封存、分割或索引優化。系統預存程序 sp_spaceused 單次僅能查一張表,本程序批次處理並排序輸出。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spaceused' AND type = 'P')
DROP PROCEDURE spaceused
GO
CREATE PROCEDURE spaceused
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #temp1 (
表名 VARCHAR(200) NULL,
行數 CHAR(11) NULL,
保留空間 VARCHAR(15) NULL,
資料空間 VARCHAR(15) NULL,
索引空間 VARCHAR(15) NULL,
未使用空間 VARCHAR(15) NULL
)
DECLARE @objname NVARCHAR(776)
DECLARE @cmd NVARCHAR(1000)
-- 遍歷所有使用者資料表
DECLARE curTab CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U' ORDER BY name
OPEN curTab
FETCH NEXT FROM curTab INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
-- 呼叫 sp_spaceused 填入暫存表
SET @cmd = N'INSERT INTO #temp1 EXEC sp_spaceused ' + QUOTENAME(@objname)
EXEC sp_executesql @cmd
FETCH NEXT FROM curTab INTO @objname
END
CLOSE curTab
DEALLOCATE curTab
-- 依保留空間 (KB) 數值排序輸出
SELECT 表名, 行數, 保留空間, 資料空間, 索引空間, 未使用空間
FROM #temp1
ORDER BY CAST(REPLACE(保留空間, ' KB', '') AS INT) DESC
DROP TABLE #temp1
END
GO
| 欄位 | 說明 |
|---|---|
| 表名 | 資料表名稱 |
| 行數 | 資料列數 (來自 sysindexes.rows) |
| 保留空間 | 資料表保留總空間 (含索引、未使用) |
| 資料空間 | 實際資料佔用空間 |
| 索引空間 | 索引佔用空間 |
| 未使用空間 | 已保留但未使用空間 |
EXEC spaceused
-- 結果範例:
-- 表名 行數 保留空間 資料空間 索引空間 未使用空間
-- Orders 830 1,200 KB 800 KB 300 KB 100 KB
-- Customers 120 450 KB 300 KB 100 KB 50 KB
SELECT
t.name AS 表名,
p.rows AS 行數,
CAST(ROUND(SUM(a.total_pages) * 8.0 / 1024, 2) AS VARCHAR) + ' MB' AS 保留空間_MB,
CAST(ROUND(SUM(a.used_pages) * 8.0 / 1024, 2) AS VARCHAR) + ' MB' AS 使用空間_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024, 2) AS VARCHAR) + ' MB' AS 未使用空間_MB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.type = 'U'
GROUP BY t.name, p.rows
ORDER BY SUM(a.total_pages) DESC
新版查詢直接讀取 DMV,無需游標,效能更佳且單位可自訂 (KB/MB/GB)。