Tuesday, August 18, 2009

SQL 2008 : Compression TSQL for uncompressed objects

A CTE (common table expression) to build the commands to compress uncompressed objects -
WITH missingcompression
   AS (SELECT Schema_name(schema_id) + '.' + name                                                                                   AS tablename,
              'ALTER TABLE [' + Schema_name(schema_id) + '].[' + name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);' AS command
       FROM   sys.objects
              INNER JOIN sys.partitions
                ON sys.partitions.object_id = sys.objects.object_id
       WHERE  TYPE = 'u'
              AND data_compression = 0
              AND Schema_name(sys.objects.schema_id) <> 'SYS'
       UNION
       SELECT sys_schemas.name + '.' + sys_objects.name                                                                                                   AS tablename,
              'ALTER INDEX [' + sys_indexes.name + '] ON [' + sys_schemas.name + '].[' + sys_objects.name + '] REBUILD WITH ( DATA_COMPRESSION = PAGE ) ' AS command
       FROM   sys.dm_db_partition_stats partition_stats
              INNER JOIN sys.indexes sys_indexes
                ON partition_stats.[object_id] = sys_indexes.[object_id]
                   AND partition_stats.index_id = sys_indexes.index_id
                   AND sys_indexes.type_desc <> 'HEAP'
              INNER JOIN sys.objects sys_objects
                ON sys_objects.[object_id] = partition_stats.[object_id]
              INNER JOIN sys.schemas sys_schemas
                ON sys_objects.[schema_id] = sys_schemas.[schema_id]
                   AND sys_schemas.name <> 'SYS'
              INNER JOIN sys.partitions
                ON sys.partitions.[object_id] = sys_objects.[object_id]
                   AND data_compression = 0)

SELECT command FROM missingcompression ORDER BY tablename ASC, command DESC


The previous version builds all compression commmands for tables and indexes.

No comments: