question

I was waiting for about an hour, and still nobody wants to answer, so I might as well go ahead. sys.partitions view has been greatly enhanced in SQL Server 2008 Enterprise edition, and it now includes data_compression and data_compression_desc columns.

Edited the "The snippet below will not run in lesser versions" statement because this is incorrect as pointed out by Fatherjack. The snippet below will not run if the version of the SQL Server is less than 2008, because sys.partitions does not have data_compression and data_compression_desc columns, will run but return 0 records if the edition is not Enterprise or Developer, and will return records but if you have compressed tables in Enterprise or Developer edition of 2008:

select 
    s.name + '.' + o.name TableName,
    p.rows RecordCount, data_compression_desc DataCompression
    from sys.indexes i inner join sys.partitions p
        on i.[object_id] = p.[object_id] and i.index_id = p.index_id
    inner join sys.objects o
        on o.[object_id] = i.[object_id]
    inner join sys.schemas s
        on o.[schema_id] = s.[schema_id] 
    where 
        p.data_compression > 0
        and i.index_id < 2
        and o.type = 'U'
        and o.name != 'dtproperties'
    order by s.name, o.name;

Oleg