SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Compress All Objects

By Jakob Bindslet,

This script creates compression statements for all objects in all databases on the instance where the script is run. As a deafult PAGE compression is used. If row compression is desired, it be changed by setting @comp = 'ROW'.

The Script does not run the actual compression commands, this is left up to the user.

The output of the script is a table with five columns:

  • Rows - the number of rows in the object to be compressed.
  • db_name - the name of the database.
  • CompressStatement - the actual statement to be executed if the object should be compressed. The statement tries to performn the operation as an online operation. If this is not possible, the operation is automatically done as an offline operation instead.
  • create_date - the create for the object in question.
  • modify_date - the modify for the object in question.

When the generated compression statements are executed, they print what compression operation was actually executed on what object.


ONLINE:  [MyDatabase].[dbo].[Clusters];
OFFLINE: [MyDatabase].[dbo].[Nodes];
ONLINE:  [MyDatabase].[dbo].[Instances];
ONLINE:  [MyDatabase].[dbo].[Disks];


Compression is a SQL Server 2008+ Enterprise Edition feature.

The script performs compression as online operations, which requires a large amount of free space in the database. In order to minimize the impact of this, the compression statements are generated with the objects with the fewest rows first.

I would recommend running the results of this script with care, and testing it well before production use.

Total article views: 1604 | Views in the last 30 days: 1
Related Articles

Compression of all Objects

This script will compress all objects in the database.


Script for compressing a database

When you have to compress a database you’d better first see the consideration for the compression. ...


Compression estimates

The script estimate compression benefits per each object in the database for which the page compress...


Scripts for Full DB Compression at partition level

Data compression is a key feature with SQL 2008. This script, can compress all DB objects (partition...


Script to Compress Tables and Indexes

This script creates scripts to compress all tables and indexes in a database.