SQL Server 取得所有表空間佔用大小：sp_spaceused + 游標批次統計

單表查詢

EXEC sp_spaceused 'tablename';

全庫批次統計存儲過程

CREATE PROCEDURE get_tableinfo AS
BEGIN
    -- 1. 建立/清空結果表
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tablespaceinfo]') ...)
        CREATE TABLE tablespaceinfo (...);
    DELETE FROM tablespaceinfo;

    -- 2. 游標遍歷所有用戶表
    DECLARE @tablename VARCHAR(255), @cmdsql VARCHAR(500);
    DECLARE cur 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 cur;
    FETCH NEXT FROM cur INTO @tablename;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
            EXEC sp_executesql N'INSERT INTO tablespaceinfo EXEC sp_spaceused @tbname',
                N'@tbname VARCHAR(255)', @tbname = @tablename;
        FETCH NEXT FROM cur INTO @tablename;
    END
    CLOSE cur; DEALLOCATE cur;
END

執行與查詢結果

EXEC get_tableinfo;
SELECT * FROM tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved)))-2) AS INT) DESC;

結果按保留空間降序排列，快速定位大表。

現代替代方案 (SQL 2005+)

SELECT
    t.name AS 表名,
    p.rows AS 記錄數,
    SUM(a.total_pages) * 8 / 1024.0 AS 保留空間_MB,
    SUM(a.used_pages) * 8 / 1024.0 AS 使用空間_MB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 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
GROUP BY t.name, p.rows
ORDER BY 保留空間_MB DESC;

DMV 版本更標準、效能更好、無需建立暫存表。