Script to Remove SQL Server Compression

This script will identify the tables and indexes that use compression and generate a script to remove the compression. This is useful, for example, if you are attempting to move a database from Enterprise Edition to Standard Edition. Standard Edition doesn’t currently support encryption.

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’