Table Compression Cheat

Creating a table with compression...

CREATE TABLE dbo.testtable (testcolumn1 char(1000), testcolumn2 int) WITH (DATA_COMPRESSION = PAGE);

Changing a table to take advantage of compression functionality...

ALTER TABLE dbo.testtable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); 

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

page compression...

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

row compression...

SELECT 'ALTER TABLE [' + 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 TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.objects where TYPE = 'u'