Script to Remove SQL Server Compression

sqlserver2014

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’
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s