[转] SQL Server 2005索引碎片整理SQL语句

/*******************************************************/
/*         效率:SQL Server 2005索引碎片整理            */
/*              逻辑碎片>=30重建索引,<30重复协会索引   */
/*         作者:贾桂军                                 */
/*         日期:2008年6月11日                          */
/*******************************************************/
/***********SQL Server
2005索引碎片整理*****************/
/**使用形式:将急需整理索引碎片的数据库设置为眼前数据库**/
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
SQL Server,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

*/

相关文章