Script to Compress All Tables
Published: Jan 22, 2017
This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.

If you want ROW level then simply substitute in the code, but this was a script I needed recently and therefore wanted accessible via my website and therefore it’s on here but for PAGE level by default. It really isn’t hard to change to a row compression version.

Also note that compressing a clustered index does NOT compress all non-clustered indexes, therefore this script does all indexes on each table.

Well here’s the script… I hope it helps anyone who needs to compress their tables. Note that if you want to run compression then if you compress only the Clustered Index then this does NOT compress non-clustereds… therefore you need to do the entire table and all indexes, which is what this script achieves…

declare @compress table
id int identity,
enableCommand varchar(max),
indexType tinyint
insert into @compress
select distinct 'alter index ' + + ' on [' + + '].[' + + '] rebuild with (data_compression = page)',
sys.indexes i
join sys.objects o
on i.object_id = o.object_id
join sys.schemas s
on o.schema_id = s.schema_id
where i.type > 0
and o.is_ms_shipped = 0
/* use the below if you want specific schema or table */
--and = 'sales'
--and = 'salesOrderDetail'
order by i.type

@counter int = 1, @sql varchar(max)

while @counter <= (select max(id) from @compress)
@sql = enableCommand
from @compress
where id = @counter

print @sql

select @counter += 1

You may not want to compress all your tables, therefore maybe limit this to only certain names or ones with specific rowcounts or data size… so I've left the option there to amend for specific tables or even schemas.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron