Auto compression all indexs on all online db

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dba_indexCompression](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Script] [nvarchar](max) NOT NULL
) ON [PRIMARY]

GO




USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Description:Auto index compression
-- =============================================
ALTER PROCEDURE [dbo].[Index_Compression]
AS
BEGIN

SET NOCOUNT ON;
DECLARE @sql nvarchar (max);
DECLARE @db nvarchar(50);
DECLARE @cnt INT;
IF (object_id('tempdb..#ContrDecomp') IS NOT NULL) DROP TABLE #ContrDecomp
IF (object_id('tempdb..#Scripts') IS NOT NULL) DROP TABLE #Scripts
CREATE TABLE #ContrDecomp ([DBName] varchar (256),[SchemaName] varchar (256),[TableName] varchar (256), [IndexName] varchar(512), [PartitionNumber] int, [DataCompression] varchar (32), [RowCount] int)
CREATE TABLE #Scripts (Scripts varchar(max))

DECLARE db_online INSENSITIVE CURSOR FOR 
(SELECT [name] FROM sys.database_mirroring M
inner join sys.databases DB
ON M.database_id=DB.database_id
WHERE name not in ('tempdb', 'model') AND db.source_database_id IS NULL
AND state_desc = 'ONLINE')
FOR  READ ONLY 
OPEN db_online
FETCH NEXT FROM db_online INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'INSERT INTO #ContrDecomp
SELECT '+''''+@db+''''+',
   sch.name as SchemaName,
   tbl.name as TableName,
       i.name as IndexName,
       p.partition_number AS [PartitionNumber],
       p.data_compression_desc AS [DataCompression],
       p.rows  AS [RowCount]
  FROM ['+@db+'].sys.tables AS tbl
  LEFT JOIN ['+@db+'].sys.indexes AS i ON (i.index_id > 0 AND i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  INNER JOIN ['+@db+'].sys.partitions AS p ON p.object_id = CAST(tbl.object_id AS int) AND p.index_id = CAST(i.index_id AS int)
  INNER JOIN ['+@db+'].sys.schemas sch ON tbl.schema_id=sch.schema_id
  WHERE p.data_compression_desc <> ''PAGE'' AND
        p.rows >= 1000000
  ORDER BY p.rows DESC, 3'
  
  EXEC (@sql)
FETCH NEXT FROM db_online INTO @db

END
CLOSE db_online
DEALLOCATE db_online

TRUNCATE TABLE [master].[dbo].[dba_indexCompression]
INSERT INTO [master].[dbo].[dba_indexCompression] (Script)
SELECT 'ALTER INDEX ['+cd.IndexName+'] ON ['+cd.DBName+'].['+cd.SchemaName+'].['+cd.TableName+'] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )' as [Script] FROM #ContrDecomp Cd
DROP TABLE #ContrDecomp


SET @cnt = (SELECT MAX([Id]) FROM [master].[dbo].[dba_indexCompression])
IF @cnt = 0 RETURN
ELSE
WHILE @cnt <> 0
BEGIN
 BEGIN TRY
 SET @sql = (SELECT [Script] FROM [master].[dbo].[dba_indexCompression] WHERE [id] = @cnt)
 SET @cnt = @cnt -1
 EXEC (@sql)
 END TRY
 BEGIN CATCH
 SET @sql = (SELECT  REPLACE(@sql, 'ONLINE = ON', 'ONLINE = OFF' ))
 SET @cnt = @cnt -1
 EXEC (@sql)
 END CATCH
 END

END