SQL Server stored procedure for bulk PAGE/ROW compression (or decompression) of all tables and their indexes in a database. Supports filtering by schema or table name, dry-run mode to preview commands before executing, and a time cap to fit within maintenance windows.

CompressTables.sql

-- ============================================================
-- Procedure for bulk compression/decompression of tables and indexes,
-- with dry-run option to validate impact before executing.
-- Run outside of load window.
-- ============================================================

USE [THE_DATABASE_TO_BE_COMPRESSED];
GO

-- Identify where this ran
select  
    LEFT(@@SERVERNAME, 50) as ServerName_Global_Variable,     
    CAST(SERVERPROPERTY('InstanceName') as nvarchar(50)) as InstanceName, 
    DB_NAME() as DBName,
    CAST(SERVERPROPERTY('ServerName') as nvarchar(50)) as ServerName, 
    CAST(SERVERPROPERTY('MachineName') as nvarchar(50)) as MachineName, 
    CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as nvarchar(50)) as  ComputerNamePhysicalNetBIOS,
	CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)) as SqlProductVersion,
    REPLACE(@@VERSION, char(10), ' | ') as SqlVersion;
GO

/*
Compression Maintenance Procedure

Purpose:
    Performs table compression maintenance in the current database by identifying eligible tables
    and applying the configured compression strategy to reduce storage footprint and improve
    I/O efficiency where appropriate.

Behavior:
    - Scans the target tables based on the procedure's selection criteria.
    - Applies the required compression option to each eligible table or index structure.
    - Skips objects that do not meet the configured conditions for compression.
    - Reports progress and the action taken for each processed object.

Scope:
    Intended for occasional storage optimization and maintenance of user tables in the target database.

Notes:
    The exact compression method and eligibility rules are controlled by the procedure parameters
    and internal filtering logic.
*/
CREATE OR ALTER proc [dbo].[CompressTables]
(
	@schema sysname = null,       -- If null, considers all schemas
	@table sysname = null,        -- If null, considers all tables
	@dryrun bit = 0,              -- If 1, only prints the commands that would be executed, without actually running them
	@compress bit = 1,            -- If 1, compresses. If 0, decompresses tables
	@rowCompression bit = 0,      -- If 1, uses ROW compression instead of PAGE compression (lighter option, useful for tables with many updates)
	@maxMinutes int = null        -- Maximum execution time in minutes
) 
as 
begin
	set nocount on;

	declare @startTime datetime;
	set @startTime = GetUtcDate();

	declare @msg nvarchar(max);

	-- Compression is supported starting from SQL Server 2016 SP1 (version 13.0.4001). Check the version and exit if not supported.
	IF (SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)) < '13.0.4001'
    BEGIN		
		set @msg = N'Compression not supported on this SQL Server version.';
        RAISERROR (@msg, 0, 1) WITH NOWAIT;
        RETURN 1;
    END;

	if (@dryrun = 1)
	begin
		set @msg = N'DRY RUN mode: nothing will be executed.';
		RAISERROR (@msg, 0, 1) WITH NOWAIT;
	end;

	-- Select the compression type to apply
	declare @targetCompressionDesc varchar(20);
	set @targetCompressionDesc = 'PAGE';
	if (@rowCompression = 1)
	begin
		set @targetCompressionDesc = 'ROW';
	end;

	if (@compress = 0)
	begin
		set @targetCompressionDesc = 'NONE';
		set @msg = N'Tables will be DECOMPRESSED!';
		RAISERROR (@msg, 0, 1) WITH NOWAIT;
	end;

	-- Candidate tables for compression
	select s.[name] as SchemaName, t.[name] as TableName
		into #target
		from sys.tables t
			inner join sys.schemas s on s.schema_id = t.schema_id
			inner join sys.objects o on o.object_id = t.object_id
		where
			(o.[type] = 'U') and
			(@schema is null or s.[name] = @schema) and
			(@table is null or t.[name] = @table);

	declare @total int;
	select @total = count(*) from #target;

	set @msg = 'Total candidate tables: ' + cast(@total as nvarchar(10));
	RAISERROR (@msg, 0, 1) WITH NOWAIT;

	-- Build compression/decompression commands for tables

	SELECT
			s.[name] as SchemaName, t.[name] AS [Table], [p].[partition_number] AS [Partition],
			[p].[data_compression_desc] AS [Compression],
			'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=' + @targetCompressionDesc + ');' as Cmd
		into #cmd
		FROM
			[sys].[partitions] AS [p]
				INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
				inner join sys.schemas s on s.schema_id = t.schema_id
				inner join sys.objects o on o.object_id = t.object_id
				inner join #target ta on ta.SchemaName = s.[name] and ta.TableName = t.[name]
		where
			(
				-- Compression
				(@compress = 1 and [p].[data_compression_desc] not in ('PAGE', 'ROW')) or
				-- Decompression
				(@compress = 0 and [p].[data_compression_desc]     in ('PAGE', 'ROW'))
			)
			and [p].[index_id] in (0,1)
			and o.[type] = 'U'
		ORDER BY p.[rows] DESC;


	-- Build compression commands for indexes
	insert into #cmd
		SELECT
				s.[name] as SchemaName, t.[name] as [Table],
				[p].[partition_number] AS [Partition],
				[p].[data_compression_desc] AS [Compression],
				'ALTER INDEX '+ '[' + i.[name] + ']' + ' ON ' + '[' + s.[name] + ']' + '.' + '[' + t.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=' + @targetCompressionDesc + ');' as Cmd
			FROM [sys].[partitions] AS [p]
			INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
			inner JOIN sys.schemas s on s.schema_id =  t.schema_id
			INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
			INNER JOIN #target ta on ta.SchemaName = s.[name] and ta.TableName = t.[name]
			WHERE
				([p].[index_id] > 1) and
				(
					-- Compression
					(@compress = 1 and [p].[data_compression_desc] not in ('PAGE', 'ROW')) or
					-- Decompression
					(@compress = 0 and [p].[data_compression_desc]     in ('PAGE', 'ROW'))
				)
			ORDER BY p.[rows] DESC;


	set @msg = 'Total commands to execute: ' + cast((select count(*) from #cmd) as nvarchar(10));
	RAISERROR (@msg, 0, 1) WITH NOWAIT;

	-- Compression execution loop
	declare @elapsedMinutes int;
	declare @cmd nvarchar(max);

	declare workCursor cursor for
		select Cmd from #cmd;

	open workCursor;
	fetch next from workCursor into @cmd;
	while @@FETCH_STATUS = 0
	begin

		set @elapsedMinutes = datediff(minute, @startTime, GetUtcDate());
		if @maxMinutes is not null and @elapsedMinutes >= @maxMinutes
		begin
			set @msg = N'Maximum execution time reached (' + cast(@elapsedMinutes as nvarchar(10)) + N' minutes). Stopping execution.';
			RAISERROR (@msg, 0, 1) WITH NOWAIT;
			break;
		end;

		RAISERROR (@cmd, 0, 1) WITH NOWAIT
		if @dryrun = 0
		begin
			execute (@cmd);
		end
		fetch next from workCursor into @cmd;
	end;
	close workCursor;
	deallocate workCursor;

	drop table #cmd;
	drop table #target;

	print N'Execution complete!';

	RETURN 0;
end;
GO

-- Dry-run execution to preview what would run
exec dbo.CompressTables @dryrun = 1;
GO

-- Example of real execution (uncomment to run), with a 60-minute time cap
-- exec dbo.CompressTables @maxMinutes = 60;