萬盛學電腦網

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

實用預存程序 (一):列出所有使用者資料表空間使用量並按大小排序

應用場景

DBA 需快速掌握各資料表佔用磁碟空間與列數,找出大表進行封存、分割或索引優化。系統預存程序 sp_spaceused 單次僅能查一張表,本程序批次處理並排序輸出。

預存程序: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

現代化改寫 (SQL Server 2005+ 推薦)

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

copyright © 萬盛學電腦網 all rights reserved