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.
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.