Set DATA_COMPRESSION for all tables

Working on a quick little project to sync up two databases and I realized the data compression was set differently between the two of them. Apparently, my Google fu was not up to snuff because it took quite a bit of stumbling around to find a forum post matching what I wanted to do.  There were actually a few scripts which used sp_MSforeachtable. For whatever reason I ended up with errors about SET QUOTED_IDENTIFIER which I couldn’t resolve (yes, I know it’s either OFF or ON so why I couldn’t get it right might be an indication of my mental state or lack of caffeine).  The script which I ended up using actually generates a script for each table in the database and gives you the option to set the DATA_COMPRESSION to ROW, PAGE, or NONE.

The script which worked was as follows:

DECLARE @Compressiontype VARCHAR(5)
SET @Compressiontype = 'PAGE' -- Value should be NONE or ROW or PAGE
SELECT 'ALTER TABLE [' + SCHEMA_NAME (schema_id)+'].['+ name 
+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+ @Compressiontype + ')'  FROM sys.tables

Note: Make sure to SET QUOTED_IDENTIFER ON before executing.

That’s it. I’ve got nothing witty today. No pop culture tie in. Nothing.

Vidhya Sagar’s MSDN forum post is here.


Leave a Reply

Your email address will not be published.