SQL Server 2005Ë÷ÒýËéƬÕûÀíSQLÓï¾ä

/*******************************************************/
/*         ¹¦ÄÜ:SQL Server 2005Ë÷ÒýËéƬÕûÀí            */
/*              Âß¼­ËéƬ>=30Öؽ¨Ë÷Òý,<30ÖØÐÂ×éÖ¯Ë÷Òý   */
/*******************************************************/
/***********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
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 sy