Script to fix ALLOW_PAGE_LOCKS option on all indexes of all databases on a particular instance

ALLOW_PAGE_LOCKS_FIX.sql

/***********************************
Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on 
                 all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO


DECLARE @DBName NVARCHAR(255)
DECLARE @IndexCount int
DECLARE @UpdateIndexQuery Varchar(500)
--- Table variable to hold intermediate result set
            DECLARE @IndexsInfo TABLE
            (  
            RowNo int identity(1,1),
            DatabaseName varchar(100),
            SchemaName VARCHAR(100),
            TableName varchar(100),
            IndexName varchar(100)
            )
--- Cursor to work on each changeable index of each db on an instance
      DECLARE DatabaseList CURSOR  
                  FOR 
                    SELECT Name
                    FROM sys.databases
                    WHERE state_desc = 'ONLINE'
                    AND is_read_only = 0
                    ORDER BY name
               
      OPEN DatabaseList
             FETCH NEXT FROM DatabaseList INTO @DBName
             WHILE @@FETCH_STATUS = 0
             BEGIN    
               INSERT INTO @IndexsInfo (DatabaseName,SchemaName,TableName,IndexName)
               EXEC( '  SELECT '''+@DBName+''' AS DatabaseName,SCHEMA_NAME(schema_id) AS SchemanName, TABL.NAME AS TableName,indx.NAME AS IndexName      
                              FROM '+@DBName+'.SYS.INDEXES indx
                              LEFT OUTER JOIN '+@DBName+'.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
                              WHERE ALLOW_PAGE_LOCKS = 0           -- where page lock option is not selected 
                              AND indx.name NOT LIKE ''QUEUE%''  ' -- we need only user defined indices
                        )
            FETCH NEXT FROM DatabaseList INTO @DBName
             END
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
-----Update allow_page_locks option for those indexes where we need  
 SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
      WHILE @IndexCount >0   
      BEGIN
          SET @UpdateIndexQuery=( SELECT ' ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
            IndexsInfo.DatabaseName+'].['+SchemaName+'].['+IndexsInfo.TableName+']
            SET (
                  ALLOW_PAGE_LOCKS = ON
                  ) ; '
            FROM @IndexsInfo AS IndexsInfo
            WHERE IndexsInfo.RowNo=@IndexCount)
      
            EXEC(@UpdateIndexQuery)
         
           SET @IndexCount=@IndexCount-1
      END