萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 查詢資料表空間使用量:sp_spaceused 與 sp_MSforeachtable 實戰

單一資料表空間查詢

快速統計 (系統統計資訊,大量異動後可能不準)


EXEC sp_spaceused 'TableName';

精確掃描 (掃描頁面,耗時較久但準確)


EXEC sp_spaceused 'TableName', @updateusage = N'TRUE';
-- 或
EXEC sp_spaceused 'TableName', true;

整個資料庫空間總覽


EXEC sp_spaceused;  -- 不帶參數回傳資料庫級摘要

批次查詢所有使用者資料表空間

方法一:快速統計 (不可靠)


EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';

方法二:精確掃描 (大資料庫慎用,會掃描所有頁面)


EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'', true';

方法三:自訂暫存表收集並排序 (推薦生產環境)


CREATE TABLE #t (
    name VARCHAR(200),
    rows INT,
    reserved VARCHAR(25),
    data VARCHAR(25),
    index_size VARCHAR(25),
    unused VARCHAR(25)
);

INSERT INTO #t EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';

SELECT TOP 100 * FROM #t ORDER BY rows DESC;

DROP TABLE #t;

sp_MSforeachtable 參數詳解 (未公開但廣泛使用)

參數型別預設說明
@command1nvarchar(2000)-第一條對每個表執行的 SQL
@replacecharnchar(1)?表名佔位符
@command2nvarchar(2000)NULL第二條 SQL
@command3nvarchar(2000)NULL第三條 SQL
@whereandnvarchar(2000)NULL額外 WHERE 條件篩選表 (如 AND o.name LIKE 't%')
@precommandnvarchar(2000)NULL迴圈前執行
@postcommandnvarchar(2000)NULL迴圈後執行

進階範例

列印表名 + 空間 + 列數


EXEC sp_MSforeachtable 
    @command1 = 'PRINT ''?''',
    @command2 = 'EXEC sp_spaceused ''?''',
    @command3 = 'SELECT COUNT(*) FROM ?';

所有資料庫空間


EXEC sp_MSforeachdb 
    @command1 = 'PRINT ''?''',
    @command2 = 'EXEC sp_spaceused';

檢查所有資料庫完整性


EXEC sp_MSforeachdb 
    @command1 = 'PRINT ''?''',
    @command2 = 'DBCC CHECKDB (?)';

更新特定前綴表統計資訊


EXEC sp_MSforeachtable 
    @whereand = "AND o.name LIKE 't%'",
    @replacechar = '*',
    @precommand = "PRINT 'Updating Statistics...'",
    @command1 = "UPDATE STATISTICS *",
    @postcommand = "PRINT 'Complete!'";

清空/截斷所有表資料 (極度危險!)


EXEC sp_MSforeachtable 'DELETE FROM ?';        -- 記錄 Log、可 Rollback、觸發 Trigger
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?';     -- 極快、最小 Log、不可 Rollback、不觸發 Trigger

@whereand 進階用法


-- 僅處理 Table1, Table2, Table3
EXEC sp_MSforeachtable 
    @command1 = "UPDATE ? SET Note = '' WHERE Note IS NULL",
    @whereand = "AND o.name IN ('Table1','Table2','Table3')";

「?」佔位符的威力

類似 DOS 萬用字元,使批次處理單一指令套用至所有表成為可能,是 sp_MSforeachtable 與 sp_MSforeachdb 成為神級工具的關鍵。

總結

sp_spaceused 是查詢空間的標準工具;sp_MSforeachtable 則將其批次化。生產環境建議使用「暫存表收集 + 精確掃描」模式,並謹慎使用清空/截斷指令。定期監控前 100 大表成長趨勢,是 DBA 容量規劃的基本功。

copyright © 萬盛學電腦網 all rights reserved