compress all tables in a SQL Server database

compress_all_tables.sql

--compress all uncompressed tables and indexes in a SQL SERVER 2017 or later database
--modify the dabase name below
USE XXDATABASE_NAMEXX;

DECLARE @sqltext NVARCHAR(MAX)

SET @sqltext = N'USE ' + DB_NAME() + N';' + NCHAR(13) + NCHAR(10)

SELECT @sqltext = @sqltext + N'ALTER ' + CASE 
		WHEN p.index_id IN (0,1)
			THEN N'Table [' + s.name + N'].[' + t.name
		ELSE N'Index [' + i.name + N'] ON [' + s.name + N'].[' + t.name
		END + N'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' + NCHAR(13) + NCHAR(10)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.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.dm_db_partition_stats AS ps ON i.[object_id] = ps.[object_id]
	AND ps.[index_id] = i.[index_id]
WHERE t.type = 'U'
	AND p.data_compression_desc = 'NONE'
ORDER BY ps.reserved_page_count

PRINT @sqltext
--add a comment to the PRINT statement above and uncomment the line below to execute the compression
--EXEC sp_sqlexec @sqltext