I wrote a script to generate script for non-clustered index page compression in SQL Server. I’ve since made improvements and here it is.

Highlights:

• This script generates index compression script on a partition by partition basis, if the underlying index is partitioned;
• Like the previous version, it still works for non-partitioned indexes;
• Like the previous version, it compresses indexes from smallest to largest, progressively saving space as it nibbles forward. Therefore it is unlikely that it’ll grow data file(s) during rebuild;
• Unlike the previous version, I’ve set maxdop = 0, which let’s SQL decides degree of parallelism;
• There is no quick way, that I know of, to tell if an underlying index is partitioned. Hence the usage of two temp tables to differentiate the two for proper script generation.

IF Object_id('tempdb..#NonPartitionedIndex') IS NOT NULL 
  DROP TABLE #nonpartitionedindex 

SELECT object_id, 
       index_id 
INTO   #nonpartitionedindex 
FROM   sys.partitions 
WHERE  object_id > 255 
       AND data_compression IN ( 0, 1 ) -- Non-compressed 
       AND index_id > 1 
GROUP  BY object_id, 
          index_id 
HAVING Count(*) = 1 

IF Object_id('tempdb..#PartitionedIndex') IS NOT NULL 
  DROP TABLE #partitionedindex 

SELECT object_id, 
       index_id 
INTO   #partitionedindex 
FROM   sys.partitions 
WHERE  object_id > 255 
       AND data_compression IN ( 0, 1 ) -- Non-compressed 
       AND index_id > 1 
GROUP  BY object_id, 
          index_id 
HAVING Count(*) > 1 

SELECT s.NAME 
       AS 
       SchemaName, 
       t.NAME 
       AS TableName, 
       i.NAME 
       AS IndexName, 
       'ALTER INDEX ' + i.NAME + ' ON ' + s.NAME + '.' 
       + t.NAME 
       + 
' REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE);' AS 
       AlterRebuild, 
       Sum(a.total_pages) * 8 
       AS TotalSpaceKB, 
       Sum(a.used_pages) * 8 
       AS UsedSpaceKB, 
       ( Sum(a.total_pages) - Sum(a.used_pages) ) * 8 
       AS UnusedSpaceKB
FROM   sys.tables t 
       JOIN sys.schemas s 
         ON s.schema_id = t.schema_id 
       JOIN sys.indexes i 
         ON t.object_id = i.object_id 
       JOIN sys.partitions p 
         ON i.object_id = p.object_id 
            AND i.index_id = p.index_id 
       JOIN sys.allocation_units a 
         ON p.partition_id = a.container_id 
       JOIN #nonpartitionedindex npi 
         ON p.object_id = npi.object_id 
            AND p.index_id = npi.index_id 
WHERE  i.index_id > 1 -- Non-clustered indexes  
       AND p.data_compression IN ( 0, 1 ) -- Non-compressed  
       AND t.NAME <> 'dtproperties' -- Ignore certain tables  
       AND t.is_ms_shipped = 0 
       AND i.object_id > 255 -- Non-system objects  
GROUP  BY s.NAME, 
          t.NAME, 
          i.NAME, 
          p.partition_number 
UNION ALL 
SELECT s.NAME                                         AS SchemaName, 
       t.NAME                                         AS TableName, 
       i.NAME                                         AS IndexName, 
       'ALTER INDEX ' + i.NAME + ' ON ' + s.NAME + '.' 
       + t.NAME 
       + 
' REBUILD WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0, DATA_COMPRESSION = PAGE ON PARTITIONS (' 
+ Cast(p.partition_number AS VARCHAR(3)) 
+ '));'                                        AS AlterRebuild, 
Sum(a.total_pages) * 8                         AS TotalSpaceKB, 
Sum(a.used_pages) * 8                          AS UsedSpaceKB, 
( Sum(a.total_pages) - Sum(a.used_pages) ) * 8 AS UnusedSpaceKB
FROM   sys.tables t 
       JOIN sys.schemas s 
         ON s.schema_id = t.schema_id 
       JOIN sys.indexes i 
         ON t.object_id = i.object_id 
       JOIN sys.partitions p 
         ON i.object_id = p.object_id 
            AND i.index_id = p.index_id 
       JOIN sys.allocation_units a 
         ON p.partition_id = a.container_id 
       JOIN #partitionedindex pi 
         ON p.object_id = pi.object_id 
            AND p.index_id = pi.index_id 
WHERE  i.index_id > 1 -- Non-clustered indexes  
       AND p.data_compression IN ( 0, 1 ) -- Non-compressed  
       AND t.NAME <> 'dtproperties' -- Ignore certain tables  
       AND t.is_ms_shipped = 0 
       AND i.object_id > 255 -- Non-system objects  
GROUP  BY s.NAME, 
          t.NAME, 
          i.NAME, 
          p.partition_number 
ORDER  BY UsedSpaceKB