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

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

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