一个删除指定表的所有索引和统计的过程

------------------------------------------------------------------------

-- Author : HappyFlyStone

-- Date   : 2009-09-05 00:57:10

-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

--      Apr 14 2006 01:12:25

--          Copyright (c) 1988-2005 Microsoft Corporation

--          Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

--     

------------------------------------------------------------------------

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) -- 存放动态组织而成的DROPS  index/stats 语法

    SET NOCOUNT ON

    --  check table

    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)

    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

    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--过程不处理CONSTRAINTS

    END

    PRINT N'------索引删除列表------'

    PRINT (@drop_idx_string+';')

    EXEC( @drop_idx_string+';')

    PRINT ('......'+CHAR(13)+CHAR(10)+'......')

    PRINT N'------索引删除结束------'

END

GO

create clustered index idx_id on ta(id)

create index idx_col on ta(col)

go

 

sp_DropAllIndex 'ta'

 

/*

------索引删除列表------

DROP INDEX ta.idx_id;

DROP INDEX ta.idx_col;

   

......

......

------索引删除结束------

*/

 本文转自：http://blog.csdn.net/happyflystone/archive/2009/09/05/4521568.aspx