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.


By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *