It's in sys.partitions.
From BoL
data_compression int
Indicates the state of compression for each partition:
0 = NONE
1 = ROW
2 = PAGE
sys.partitions has both object_id and index_id, so you can join it in on those 2, if you have partitioned tables (or indexes), there'll be more than 1 row for an index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass