Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

2.8 (5)

You rated this post out of 5. Change rating