>>> >>>-- list fragmented indexes >>>SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, AA.Name, b.name, ps.avg_fragmentation_in_percent >>>FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps >>> INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id >>> INNER JOIN sys.tables as AA ON b.OBJECT_ID=AA.OBJECT_ID >>>WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent>30 AND NOT b.name IS NULL >>>ORDER BY 6 DESC >>>GO >>> >>>--rebuild fragmented indexes >>> DECLARE @lorsData CURSOR >>> DECLARE @lcTABNAME VARCHAR(128), @lcNAME VARCHAR(128), @lfDFG FLOAT >>> SET @lorsData=CURSOR LOCAL READ_ONLY FOR >>> SELECT AA.Name, b.name,ps.avg_fragmentation_in_percent >>> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps >>> INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id >>> INNER JOIN sys.tables as AA ON b.OBJECT_ID=AA.OBJECT_ID >>> WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent>30 AND NOT b.name IS NULL >>> ORDER BY 3 DESC >>> >>> OPEN @lorsData >>> WHILE 0 = 0 BEGIN >>> FETCH NEXT FROM @lorsData INTO @lcTABNAME, @lcNAME, @lfDFG >>> IF @@FETCH_STATUS <> 0 BREAK >>> >>> BEGIN TRY >>> EXEC('ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD WITH(ONLINE = ON)') >>> PRINT CAST(@lfDFG AS VARCHAR(30))+' ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD /*WITH(ONLINE = ON)*/' >>> END TRY >>> BEGIN CATCH >>> PRINT 'FAILED: '+@lcTABNAME+' '+@lcNAME >>> END CATCH >>> >>> END >>> CLOSE @lorsData >>> DEALLOCATE @lorsData >>>GO >>>>>>
>>FAILED: invntory inv_descr >>>>
> EXEC('ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD ') >>
OPEN @lorsData WHILE 0 = 0 BEGIN FETCH NEXT FROM @lorsData INTO @lcTABNAME, @lcNAME, @lfDFG IF @@FETCH_STATUS <> 0 BREAK BEGIN TRY EXEC('ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD ') PRINT CAST(@lfDFG AS VARCHAR(30))+' ALTER INDEX ['+@lcNAME+'] ON ['+@lcTABNAME+'] REBUILD /*WITH(ONLINE = ON)*/' END TRY BEGIN CATCH PRINT 'FAILED: '+@lcTABNAME+' '+@lcNAME END CATCH END CLOSE @lorsData DEALLOCATE @lorsDataThank you!