First of all, I've already evaluated and am using SQL 2008 Table & Index Compression.
This procedure is to help you to do the same. Obviously if you're reading this you're most likely a DBA and will dutifully evaluate whether compression is appropriate for your environment. Personally I'm very impressed and feel the storage savings far outweigh the additional processor overhead.
UncompressedObjects has 2 required parameters, @database and @compressiontype.
@database is the name of the database you want to compress.
@compressiontype is either PAGE or ROW compression.
There are 2 optional parameters @emailrecipients and @emailprofile.
If these are both supplied, the output is sent to the email addresses supplied (providing you have an email profile configured).
The procedure provides >
- Lists of tables & indexes without compression
- Lists of tables & indexes not using the desired compression (e.g. ROW when you've specified a compression type of PAGE)
- TSQL commands to compress the database objects.
@database = 'MyDatabase'
, @compressiontype = 'ROW'
@database = 'Adventureworks'
, @compressiontype = 'PAGE'
, @emailrecipients = 'firstname.lastname@example.org'
, @emailprofile = 'Profile Name'
I've also supplied stored procedure 'UncompressedServerObjects' which simply executes UncompressedObjects for each user database. I schedule this via SQL Agent so I know if objects are created without compression.
I hope people find this script useful.