Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 1565 | Views in the last 30 days: 2
Related Articles

Compression of all Objects

This script will compress all objects in the database.


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.


A Brute Force Way to Compress a Database

There are a handful of scripts out there to compress all of of the objects in your SQL 2008 database...


A Collaborative Approach To Table Compression

A start at identifying and creating TSQL statements for tables that may benefit from compression.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones