查詢SQL Server資料表空間佔用：sp_spaceused與批次統計存儲過程

單表查詢

EXEC sp_spaceused 'tablename'
-- 回傳：name, rows, reserved, data, index_size, unused

批次統計所有使用者表存儲過程

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)
    )

  DELETE FROM tablespaceinfo

  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
GO

-- 執行
EXEC get_tableinfo

-- 查詢結果（按保留空間降冪）
SELECT * FROM tablespaceinfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved)))-2) AS INT) DESC

說明

• 游標遍歷sysobjects篩選使用者表（排除暫存表#%）。
• sp_executesql參數化執行sp_spaceused，防注入、利用計畫快取。
• 結果表reserved等欄位含單位（如12345 KB），排序時需剝離單位轉數值。

此法可定期排程執行，歷史記錄空間增長趨勢，輔助容量規劃。