Before compressing any database, a check to see whether ROW or PAGE compression will give the best result is necessary before carrying out any change. The script below will generate a set of scripts to run and the output you can use to determine what level of compression you should use – based on what (if any) savings will be gained with a certain compression type. The stored procedure that determines this is sp_estimate_data_compression_savings. Obviously you could compress all tables however you will get more bang for your buck if you compress the larger tables so I’ve purposely set the script to only show those tables with 50,000 or more pages.

SELECT
'EXEC sys.sp_estimate_data_compression_savings @schema_name = '+SchemaName+'
                                             , @object_name = '+TableName+'
                                             , @index_id = 1
                                             , @partition_number = NULL
                                             , @data_compression = ROW'
FROM (SELECT    SCHEMA_NAME(schema_id) AS SchemaName
              , t.name AS TableName
              , SUM(total_pages) AS TotalPages
      FROM      sys.allocation_units AS a
                JOIN sys.partitions AS p ON a.container_id = p.partition_id
                INNER JOIN sys.tables AS t ON p.object_id = t.object_id
      WHERE     p.data_compression = 0
      GROUP BY  SCHEMA_NAME(schema_id)
              , t.name
      HAVING    SUM(total_pages) > 50000
    ) AS x
ORDER BY x.SchemaName ASC