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.