SET NOCOUNT ON DECLARE @dbName NVARCHAR (128) DECLARE @SchemaName NVARCHAR (128) DECLARE @TableName NVARCHAR (128) DECLARE @IndexName NVARCHAR (128) DECLARE @avg_fragmentation_in_percent FLOAT DECLARE @nSQL NVARCHAR (4000) DECLARE @index_list TABLE ( dbName NVARCHAR (128), SchemaName NVARCHAR (128), TableName NVARCHAR (128), IndexName NVARCHAR (128), avg_fragmentation_in_percent FLOAT ) INSERT INTO@index_list SELECT DB_NAME (PS.database_id) AS dbName, S.name AS SchemaName, O.name AS TableName, 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.objects O ON PS.object_id = O.object_id INNER JOIN sys.schemas S ON S.schema_id = O.schema_id WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20 AND PS.index_type_desc IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes AND b.is_hypothetical = 0 -- Only real indexes AND O.type_desc = 'USER_TABLE' -- Restrict to user tables AND PS.page_count > 8 --- ignore tables less tha 64K --ORDER BY ps.avg_fragmentation_in_percent DESC SELECT TOP 1 @dbName = dbName, @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName, @avg_fragmentation_in_percent = avg_fragmentation_in_percent FROM @index_list WHILE (@@rowcount <> 0) BEGIN IF @avg_fragmentation_in_percent <= 40 -- REORGANIZE SET @nSQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE;' ELSE -- REBUILD SET @nSQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @dbName + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);' BEGIN TRY EXECUTE (@nSQL); END TRY BEGIN CATCH SET @nSQL = REPLACE (@nSQL, 'ONLINE = ON,', '') EXECUTE (@nSQL); END CATCH --PRINT @nSQL DELETE FROM @index_list WHERE dbName = @dbName AND SchemaName = @SchemaName AND TableName = @TableName AND IndexName = @IndexName AND avg_fragmentation_in_percent = @avg_fragmentation_in_percent SELECT TOP 1 @dbName = dbName, @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName, @avg_fragmentation_in_percent = avg_fragmentation_in_percent FROM @index_list END