One of the (optional) requirements for the Works With SQL Server 2008 test is that row level compression is enabled on all tables and indexes. We have an existing database with a lot of tables and indexes already created. Is there an easy way to enable compression on all these tables and indexes?
Here is the script I ended up making from splattne's recommendation.
select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' from sysobjects where type = 'U' -- all user tables UNION select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' from sysobjects k join sysobjects t on k.parent_obj = t.id where k.type = 'K' -- all keys AND t.type = 'U' -- all user tables