Enable row level compression on all tables and indexes

I've just used the Works With SQL Server Tool to test after compressing using the a_hardin-splattne script. The test failed because several indexes were not compressed.

The "sysobjects" view includes some but not all of the indexes. We need "sysindexes" instead. Thanks to the anonymous poster at aspfaq.com for this index insight. We also want to ignore user-defined functions.

SELECT 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM  sysobjects  WHERE type = 'U' -- all user tables
UNION
SELECT  'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.id) + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM 
    sysindexes i 
    inner join sysobjects o on o.name = OBJECT_NAME(i.id)
WHERE 
    (i.indid BETWEEN 1 AND 254) 
    AND (i.Status & 64)=0 
    AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 
    AND NOT o.type in ('TF','FN')

You could use this simple SQL script in order to create another script which should do the job:

   select 'ALTER TABLE ' + name + ' REBUILD WITH (DATA_COMPRESSION = ROW)' 
         + CHAR(13) + CHAR(10) + 'GO'
   from   sysobjects
   where  type = 'U' -- all user tables

(I didn't test this, but it should work.)

You can find a much more sophisticed script here on the SQLServerBible site (look for "db_compression procs".) Read the author's blog post "Whole Database - Data Compression Procs".

As an aside, be careful with enabling everything to be compressed. The data is compressed in memory and decompressed every time it is accessed. For an OLTP system with lots of changes and memory-resident data, compression is not suitable as you'll burn more CPU for no gain in IOs. For data that is read-occasionally, like a data warehouse, it's much more suitable because you get a big trade-off in reduced IOs against the extra CPU. Compression is a data warehousing feature, not an OLTP feature. Not sure if this applies to you, but worth pointing out just-in-case, and for others reading the thread.

One other point - it may be that you don't get a significant gain from compression so it isn't worth turning out. Best practice to check the compression gain before enabling using the sp_estimate_data_compression_savings stored-proc.

Thanks

You should probably look to handle new tables as well, so you don't need to run this batch on a regular basis. I detailed a method for automatically compressing new tables in this blog post.

I'd also mention that you should check to see whether or not the table is compressed before rebuilding it.

I'm a little late to the party, but here's a version that uses DMVs rather than the deprecated system tables and allows for arbitrary schema names. It enables or disables row or page compression on all heaps, clustered indexes and nonclustered indexes (including all partitioned tables) in the current database:

-- Enables or disables compression on all tables in the database
DECLARE @Compression NVARCHAR(4) = 'PAGE' -- NONE, ROW or PAGE
    , @Cmd NVARCHAR(MAX) = '';

-- Clustered indexes, heaps
SELECT @Cmd +=  '
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id IN (0, 1)
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Nonclustered indexes
SELECT @Cmd +=  '
ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
    INNER JOIN sys.indexes i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id > 1
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Review commands
SELECT @Cmd;

-- Run commands
--EXEC sp_executesql @Cmd;