Can anyone provide the script for rebuilding and re-indexing the fragmented index when 'avg_fragmentation_in_percent' exceeds certain limits (better if cursor is not used)?
To rebuild use:
ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REBUILD
or to reorganize use:
ALTER INDEX __NAME_OF_INDEX__ ON __NAME_OF_TABLE__ REORGANIZE
Reorganizing should be used at lower (<30%) fragmentations but only rebuilding (which is heavier to the database) cuts the fragmentation down to 0%.
For further information see https://msdn.microsoft.com/en-us/library/ms189858.aspx