您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 資料表空間使用量查詢：sp_spaceused 系統預存程序實戰應用

編輯：MySQL 資料庫相關知識

核心方法：sp_spaceused

單表查詢：

EXEC sp_spaceused 'tablename';

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

CREATE PROCEDURE get_tableinfo
AS
BEGIN
    -- 建立結果暫存表
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects 
                   WHERE id = OBJECT_ID(N'[dbo].[tablespaceinfo]') 
                   AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    CREATE TABLE tablespaceinfo (
        nameinfo VARCHAR(50),
        rowsinfo INT,
        reserved VARCHAR(20),
        datainfo VARCHAR(20),
        index_size VARCHAR(20),
        unused VARCHAR(20)
    );

    TRUNCATE TABLE tablespaceinfo;

    DECLARE @tablename VARCHAR(255);
    DECLARE @cmdsql VARCHAR(500);

    DECLARE Info_cursor CURSOR FOR
    SELECT o.name
    FROM dbo.sysobjects o
    WHERE OBJECTPROPERTY(o.id, N'IsTable') = 1
      AND o.name NOT LIKE N'#%%'
    ORDER BY o.name;

    OPEN Info_cursor;
    FETCH NEXT FROM Info_cursor INTO @tablename;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS (SELECT * FROM dbo.sysobjects 
                   WHERE id = OBJECT_ID(@tablename) 
                   AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
        BEGIN
            EXEC sp_executesql
                N'INSERT INTO tablespaceinfo EXEC sp_spaceused @tbname',
                N'@tbname VARCHAR(255)',
                @tbname = @tablename;
        END
        FETCH NEXT FROM Info_cursor INTO @tablename;
    END

    CLOSE Info_cursor;
    DEALLOCATE Info_cursor;
END
GO

執行與結果查詢

-- 執行預存程序
EXEC get_tableinfo;

-- 查詢結果，依保留空間降序排列
SELECT *
FROM tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC;

結果欄位說明

欄位	含義
nameinfo	資料表名稱
rowsinfo	資料列數
reserved	總保留空間 (含資料、索引、未使用)
datainfo	資料空間
index_size	索引空間
unused	未使用空間

進階應用建議

定期排程執行並記錄歷史趨勢，預測容量成長
結合 DBCC UPDATEUSAGE 修正空間統計誤差
大型資料庫建議分批次查詢避免長時間鎖定系統表
結果可匯出至 Excel/BI 工具製作容量儀表板

現代版本替代方案 (SQL Server 2008+)

-- 使用 DMV 查詢，效能更好、無游標開銷
SELECT 
    OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS TableName,
    p.rows AS RowCount,
    (SUM(a.total_pages) * 8) / 1024.0 AS ReservedMB,
    (SUM(a.used_pages) * 8) / 1024.0 AS DataMB,
    ((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.0 AS UnusedMB
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0,1) -- heap or clustered index
GROUP BY p.object_id, p.rows
ORDER BY ReservedMB DESC;