sql server 2005 自动重新建立索引

CREATE PROC SHANE_AutoProIndex  
AS 
 
DECLARE @tblName VARCHAR(40)  
DECLARE @indexID INT 
DECLARE @proFlag FLOAT 
DECLARE @indexName VARCHAR(40)  
DECLARE @sql varchar(200)  
 
DECLARE _tblCur CURSOR FOR SELECT TblName FROM AutoProIndexModel  
OPEN _tblCur  
FETCH NEXT FROM _tblCur INTO @tblName  
WHILE @@FETCH_STATUS = 0  
BEGIN 
PRINT 'Now is Proing: ' + @tblName  
--PRINT @tblName  
 
 DECLARE _indexCur CURSOR FOR SELECT index_id, avg_fragmentation_in_percent   
 FROM sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), OBJECT_ID(@tblName), NULL, NULL, 'LIMITED')  
   
 OPEN _indexCur  
 FETCH NEXT FROM _indexCur INTO @indexID, @proFlag  
 
 WHILE @@FETCH_STATUS = 0  
 BEGIN 
   PRINT 'The index id is: ' + CAST(@indexID AS VARCHAR(10)) + ', avg_fra_in_percent is: ' + CAST(@proFlag AS VARCHAR(20))  
   IF @proFlag > 5 AND @proFlag < 30  
    BEGIN 
     SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID  
     print @indexName + ' must be  REORGANIZE' 
     SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REORGANIZE' 
     EXEC(@sql)  
PRINT @SQL  
    END 
   ELSE IF @proFlag > 30  
    BEGIN 
     SELECT @indexName = name FROM sys.indexes WHERE [object_id] = OBJECT_ID(@tblName) AND index_id = @indexID  
     print @indexName + ' must be  REBUILD' 
     SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tblName + ' REBUILD' 
     EXEC(@sql)  
PRINT @SQL  
    END 
   FETCH NEXT FROM _indexCur INTO @indexID, @proFlag  
 END   
 
 CLOSE _indexCur  
 DEALLOCATE _indexCur  
 
print '' 
 FETCH NEXT FROM _tblCur INTO @tblName  
END 
 
CLOSE _tblCur  
DEALLOCATE _tblCur   

该PROC中有张表AutoProIndexModel，这张表里面存储的是需要维护索引的几张表名。
该PROC流程如下：
1.先使用游标读取AutoProIndexModel中的需要整理的表的信息，进行循环
2.使用DMF，sys.dm_db_index_physical_stats得出每张表中每个索引的碎片情况后，根据avg_fragmentation_in_percent 字段的值进行具体的操作
3.如果avg_fragmentation_in_percent 在5-30之间进行索引重新组织，>30则索引重建。
新建个计划任务后，定时调用该存储过程就可以实现索引的自动维护了。