This will search all databases on a server for 1. tables of a specific name 2. that are not already compressed and 3. with row counts over 10 million. The return will be a script that you can look over and decide to run all or pieces of it.
I have been dealing with compressed tables since 2008 and it has been my experience that only very large tables benefit from table compression. I have proved it in my own performance tests time and time again. Another item to consider is whether to compress by row or by page, this sample is specific to row compression.
WARNING: Be sure to set the MAXDOP to half of your available CPU.
Exec sp_MSforeachdb ‘
select ”ALTER TABLE ?.dbo.[”+ name +”] REBUILD WITH (DATA_COMPRESSION = ROW, MAXDOP = 8);”
FROM ?.dbo.sysobjects oo
inner join ?.sys.partitions SP ON oo.id = sp.object_id
WHERE oo.type = ”U”
and sp.data_compression = 0
and [rows] > 10000000
and sp.index_id in (0,1) — 0=heap , 1=clustered , 2 or greater=nonclustered index
and oo.name in (”tablename1”,”tablename2”,”tablename3′)