SQL Server 2005索引碎片整理SQL語句

set nocount on

--使用遊标重新組織指定庫中的索引,消除索引碎片

--R_T層遊标取出目前資料庫所有表

declare R_T cursor

for select name from sys.tables

declare @T varchar(50)

open r_t

fetch next from r_t into @t

while @@fetch_status=0

begin

--R_index遊标判斷指定表索引碎片情況并優化

declare R_Index cursor

for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t

  join sys.indexes i on i.object_id=t.object_id

  join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s

   on s.object_id=i.object_id and s.index_id=i.index_id

declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)

open r_index

fetch next from r_index into @TName,@Iname,@avg

while @@fetch_status=0

begin

  if @avg>=30  --如果碎片大于30,重建索引

  begin

   set @str='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' rebuild'

  end

  else   --如果碎片小于30,重新組織索引

  begin

   set @STR='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' reorganize'

  end

  print @str

  exec (@str)  --執行

  fetch next from r_index into @TName,@Iname,@avg

end

--結束r_index遊标

close r_index

deallocate r_index

fetch next from r_t into @t

end

--結束R_T遊标

close r_t

deallocate r_t

set nocount off

/*

--檢視指定表的索引情況

select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t

join sys.indexes i on i.object_id=t.object_id

join sys.dm_db_index_physical_stats(db_id(),object_id('t2'),null,null,'limited') s

  on s.object_id=i.object_id and s.index_id=i.index_id