SQL Server 刪除指定資料表所有索引與統計資料的預存程序實作

在 SQL Server 資料庫維護作業中，有時需要一次性移除某張資料表上的所有索引與統計資料，例如在大量資料匯入前為了提升效能、或是重建索引架構時。本文提供一個實用的預存程序 sp_DropAllIndex，能自動識別並刪除指定資料表的所有使用者定義索引與統計資料，並排除條件約束相關的索引，避免誤刪關鍵物件。

預存程式碼完整版

IF EXISTS (SELECT name FROM sysobjects 
           WHERE id = OBJECT_ID('sp_DropAllIndex') 
           AND OBJECTPROPERTY(OBJECT_ID('sp_DropAllIndex'),'IsProcedure')=1)
    DROP PROCEDURE sp_DropAllIndex
GO

CREATE PROCEDURE sp_DropAllIndex
    @tabname nvarchar(150)  -- 目標資料表名稱
AS
BEGIN
    DECLARE @drop_idx_string nvarchar(4000)
    SET NOCOUNT ON

    -- 檢查資料表是否存在
    IF NOT EXISTS (SELECT 1 
                   FROM INFORMATION_SCHEMA.TABLES 
                   WHERE table_type = 'base table' AND table_name = @tabname)
    BEGIN
        RAISERROR(N'------目前資料表：''%s'' 不存在!', 16, 1, @tabname)
        RETURN (1)
    END

    SET @tabname = OBJECT_ID(@tabname)

    /*==================================================
      第一階段：刪除統計資料
      status 值說明：
        96            = 僅統計資料
        10485856      = 統計資料 + 自動建立
        8388704       = 統計資料 + 使用者建立
    ==================================================*/
    IF EXISTS (SELECT 1 
               FROM sysindexes 
               WHERE id = @tabname 
                 AND indid BETWEEN 1 AND 254 
                 AND status IN (96, 10485856, 8388704))
    BEGIN
        SELECT @drop_idx_string = ISNULL(@drop_idx_string + ';', '') 
                               + ('DROP STATISTICS ' + OBJECT_NAME(@tabname) + '.' + name)
        FROM sysindexes 
        WHERE id = @tabname 
          AND indid BETWEEN 1 AND 254 
          AND status IN (96, 10485856, 8388704)
    END

    IF LEN(@drop_idx_string) > 0
    BEGIN
        PRINT N'------統計資料刪除清單------'
        PRINT @drop_idx_string + ';'
        EXECUTE(@drop_idx_string + ';')
        PRINT N'------統計資料刪除結束------'
    END

    /*==================================================
      第二階段：刪除索引
      排除條件約束索引 (PRIMARY KEY、UNIQUE 等)
    ==================================================*/
    IF EXISTS (SELECT 1 
               FROM sysindexes 
               WHERE id = @tabname 
                 AND indid BETWEEN 1 AND 254 
                 AND status NOT IN (96, 10485856, 8388704))
    BEGIN
        SET @drop_idx_string = NULL
        SELECT @drop_idx_string = ISNULL(@drop_idx_string + ';' + CHAR(13) + CHAR(10), '') 
                               + ('DROP INDEX ' + OBJECT_NAME(@tabname) + '.' + name)
        FROM sysindexes 
        WHERE id = @tabname 
          AND indid BETWEEN 1 AND 254 
          AND status NOT IN (96, 10485856, 8388704)
          AND OBJECTPROPERTY(OBJECT_ID(name), 'IsConstraint') IS NULL
    END

    PRINT N'------索引刪除清單------'
    PRINT (@drop_idx_string + ';')
    EXEC(@drop_idx_string + ';')
    PRINT ('......' + CHAR(13) + CHAR(10) + '......')
    PRINT N'------索引刪除結束------'
END
GO

使用範例

-- 建立測試資料表與索引
CREATE TABLE ta (id INT, col VARCHAR(50))
CREATE CLUSTERED INDEX idx_id ON ta(id)
CREATE INDEX idx_col ON ta(col)
GO

-- 執行預存程序
EXEC sp_DropAllIndex 'ta'

執行結果說明

程式會依序輸出：

1. 統計資料刪除清單：列出所有將被移除的統計資料指令
2. 索引刪除清單：列出所有將被移除的索引指令（已排除條件約束索引）
3. 執行完成提示

注意事項

• 本程序僅適用於 SQL Server 2005 及以上版本（使用 sysindexes 相容性檢視）
• 執行前請確認已備份資料庫或具備重建索引的腳本
• 叢集索引被刪除後，資料表會轉為堆積，可能影響查詢效能
• 條件約束相關索引（主鍵、唯一鍵）受 OBJECTPROPERTY(...,'IsConstraint') 保護，不會被刪除

適用場景

• 大量資料載入前移除索引以加速匯入
• 索引重組維護窗口期間的批次作業
• 測試環境快速重置資料表索引結構