重建数据库所有索引的脚本执行过慢,大家帮改改

godsad

use flushIndex;
go
DECLARE @db_id INT;
set @db_id = db_id (\'flushIndex\');

DECLARE @@table varchar(50)
DECLARE contact_cursor CURSOR FOR
select table_name from  INFORMATION_SCHEMA.TABLES  where TABLE_TYPE = \'BASE TABLE\';

OPEN contact_cursor

-- Perform the first fetch.
FETCH NEXT FROM contact_cursor INTO @@table

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   print @@table;
   DECLARE @object_id INT;
   set @object_id = object_id (@@table);

   DECLARE @index varchar(100);
   DECLARE contact_cursor2 CURSOR FOR
   select six.name from sys.indexes as six
   inner join sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , \'LIMITED\') as ix
   on six.index_id = ix.index_id and six.object_id=@object_id
   where ix.avg_fragmentation_in_percent > 10.0 AND ix.index_id > 0;
   OPEN contact_cursor2
   FETCH NEXT FROM contact_cursor2 INTO @index
   WHILE @@FETCH_STATUS = 0
   BEGIN
      print @index;

      ALTER INDEX \"@index\" ON \"@@table\" REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF );

          FETCH NEXT FROM contact_cursor2 INTO @index
   END
   CLOSE contact_cursor2
   DEALLOCATE contact_cursor2

   FETCH NEXT FROM contact_cursor INTO @@table
END

CLOSE contact_cursor
DEALLOCATE contact_cursor