Script to Compress All Tables
Published: 5th April 2015
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)',
      
i.type
from
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

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

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

  
print @sql
  
exec(@sql)

  
select @counter += 1
end


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.
Comments:
NB: Comments will only appear once they have been moderated.