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

------------------------------------------------------------------------
-- 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;
......
......
------索引删除结束------
*/