Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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 ' + i.name + ' on [' + s.name + '].[' + o.name + '] 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 s.name = 'sales'
--and o.name = '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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.