SQL 2008 : Index Compression Cheat

Index compression is similar to table compression in terms of how you enable it, i.e. -

ALTER INDEX ALL ON schema.table REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

NB : I have done NO performance testing on the effects of compressing indexes.
This post is for information only, I have still to evaluate whether i'm compresssing indexes myself!

>>>>

Want to implement index compression on all Indexes in a sql 2008 db?
Here's dynamic sql to build those commands...

page compression...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' FROM sys.objects where TYPE = 'u'

row compression...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);' FROM sys.objects where TYPE = 'u'

and to remove it again...

SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.objects where TYPE = 'u'