USE [DATA_BASE]
SET NOCOUNT ON;

DECLARE @SQLStr NVARCHAR(MAX) = '';
DECLARE @SQLStr_on_locks NVARCHAR(MAX) = '';
DECLARE @SQLStr_off_locks NVARCHAR(MAX) = '';

DECLARE @table_name NVARCHAR(40);
DECLARE @index_name NVARCHAR(40);

SELECT
  CAST(OBJECT_NAME(ips.OBJECT_ID) AS nvarchar(40)) AS TableName
 ,CAST(i.NAME AS nvarchar(40)) AS IndexName
 ,avg_fragmentation_in_percent
 ,page_count
 ,i.allow_page_locks
 INTO #working
 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
 INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
  AND (ips.index_id = i.index_id)
 WHERE avg_fragmentation_in_percent > 5 AND page_count > 1000

-- 
SELECT TableName INTO #tables_locks FROM #working GROUP BY TableName

DECLARE db_cursor CURSOR FOR SELECT * FROM #tables_locks
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @table_name;  
WHILE @@FETCH_STATUS = 0  
BEGIN
  SET @SQLStr_on_locks = @SQLStr_on_locks + N'ALTER INDEX  ALL ON ' + @table_name + ' SET (ALLOW_PAGE_LOCKS = ON);' + CHAR(13) 
  SET @SQLStr_off_locks = @SQLStr_off_locks + N'ALTER INDEX  ALL ON ' + @table_name + ' SET (ALLOW_PAGE_LOCKS = OFF);' + CHAR(13) 

  FETCH NEXT FROM db_cursor INTO @table_name; 
END

CLOSE db_cursor  
DEALLOCATE db_cursor 

DROP TABLE #tables_locks;

-- 
EXEC sp_executesql @SQLStr_on_locks;

DECLARE db_cursor CURSOR FOR SELECT TableName, IndexName FROM #working
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @table_name, @index_name;  
WHILE @@FETCH_STATUS = 0  
BEGIN
  SET @SQLStr = N'ALTER INDEX [' + @index_name + '] ON [dbo].[' + @table_name + '] REORGANIZE WITH ( LOB_COMPACTION = ON );';
  EXEC sp_executesql @SQLStr;
  
--  PRINT N'Executed: ' + @SQLStr;
  FETCH NEXT FROM db_cursor INTO @table_name, @index_name; 
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- 
EXEC sp_executesql @SQLStr_off_locks;

DROP TABLE #working;