SQL Enterprise to SQL Standard - Handy Script to remove compression.

The following script displays the indexes and tables that use compression and generates a script that removes the compression.

SELECT DISTINCT ‘ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’ 
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = ‘u’
and data_compression_desc != ‘NONE’
UNION 
SELECT ‘ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);’
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = ‘u’
and data_compression_desc != ‘NONE’