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

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

-- 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.youkuaiyun.com/happyflystone/archive/2009/09/05/4521568.aspx