Alter Index with Rebuild

,

Run the above t-sql in the query analyser.

Declare @DBReindex_All nvarchar(4000)
Declare @DBReindex_Table nvarchar(500)

Declare @opr char(1)
select @opr=char(13), @DBReindex_All=''

If (Select Count(*) from sys.objects where type = 'U') <= 50
Begin

    Select @DBReindex_All = @DBReindex_All + COALESCE('ALTER INDEX ALL ON [' + schema_name(schema_id) + '].[' + Name + ']' + ' REBUILD WITH (FILLFACTOR = 80,STATISTICS_NORECOMPUTE = OFF)','') from Sys.objects where type = 'U' order by name


    Exec sp_Executesql @DBReindex_All     
End
Else
Begin
    
    Declare curReindex Cursor for 
        Select COALESCE('ALTER INDEX ALL ON [' + schema_name(schema_id) + '].[' + Name + ']' + ' REBUILD WITH (FILLFACTOR = 80,STATISTICS_NORECOMPUTE = OFF)','')
        
from Sys.objects where type = 'U' order by name
    Open curReindex
    Fetch next From curReindex into @DBReindex_Table
    While @@Fetch_Status = 0
    Begin
Exec sp_Executesql @DBReindex_Table        
--PRINT @DBReindex_Table        
Fetch next From curReindex into @DBReindex_Table
    End
    close curReindex
    Deallocate curReindex
End

Rate

2.8 (5)

Share

Share

Rate

2.8 (5)