Monday, September 23, 2013

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’

No comments: