I thought I'd share my final query. This'll give two result sets, the first of data compression for heaps and clustered indexes, and the second of index compression for non-clustered indexes.

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index],  
    [p].[partition_number] AS [Partition],
    [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1

While I think while the final queries posted by Barguast may work, there is still a problem with them/something not explained well enough.

Basically an index_id of 0 is a heap, 1 is a clustered index and 2 is everything else (non-clustered indexes).

The problem with the above queries is that the query for the data will not work if the table is a heap (even though there is data in the table). Also the query for the indexes works because you specify the index_Id = 2 and there is dupes due to not joining the index_id between sys.indexes and sys.partitions . If you join on those then there will not be duplicates in the result set and you can do the much more understandable index_id not in (0,1) .

Anyways fixed queries are below. I also added index name to the first query (note this field will be null if the table is a heap). Also note that you don't have to specify the join for index_id on the first query, because the where specifies (0,1) and there can only be one of those (in other words you could add it if you like but it doesn't make a difference).

-- Data (table) compression (heap or clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

-- Index compression (non-clustered index)
SELECT [t].[name] AS [Table], 
       [i].[name] AS [Index],  
       [p].[partition_number] AS [Partition],
       [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] 
     ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] 
     ON [i].[object_id] = [p].[object_id] AND i.index_id = p.index_id
WHERE [p].[index_id] not in (0,1)

These answers are all decent and work. Since I embellished it a bit for my work, I figured is was about time for to contribute back a bit. This query adds the schema from Jason's answer (which I needed). and it also sorts out some of the join issues and combines the results into a pretty simple summary.

-- Returns user tables and indexes in a DB and their Compression state
select s.name [Schema], t.name [Table], i.name [Index], p.data_compression_desc Compression
     , case when p.index_id in (0, 1) then 'Table' else 'Index' end CompressionObject
  from sys.tables t
  join sys.schemas s on t.schema_id = s.schema_id
  join sys.indexes i on t.object_id = i.object_id
  join sys.partitions p on (i.object_id = p.object_id and i.index_id = p.index_id)
where t.type = 'U'
order by 1, 2, p.index_id, 3

I used this as a "work list" to generate scripts to compress everything since I just lift-shift the db into an Azure VM and wanting to reduce IOPS to improve perf. Hope this helps somebody out there.

This should do the job, test it for a small subset to be sure it gives you what you need

SELECT DISTINCT s.name [Schema], t.name [Table], i.name [Index Name], p.partition_number, p.data_compression_desc
-- uncommenting the below line will give you dupes
--, p.index_id
FROM sys.schemas s
INNER JOIN sys.tables t
    ON s.schema_id = t.schema_id
    INNER JOIN sys.indexes i
        ON t.object_id = i.object_id
    INNER JOIN sys.partitions p
        ON t.object_id = p.object_id
ORDER BY s.name, t.name

The reason you are probably getting dupes is because you have multiple partition records per table, e.g. multiple index_id, see this MSDN article for clarification on what the index_id's mean. Adding a DISTINCT should solve the problem of the dupes

I prepared below SQL for myself. Also it can be useful for you. Here you are.

SELECT [t].[name] AS [Table_Name],
       '-' AS Index_Name,
       p.rows AS [Total_Records],
       (8 * SUM(a.used_pages))/1024   AS 'Size_MB',
       8 * SUM(a.used_pages)    AS 'Size_KB',
       p.data_compression AS "Data_Compression",
       [p].[data_compression_desc] AS [Data_Compression_Desc],
       p.xml_compression AS "XML_Compression", 
       p.xml_compression_desc AS "XML_Compression_Desc"
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE [p].[index_id] in (0,1)
AND t.name in ('Type_Your_Table_Name')
GROUP BY t.[name], p.rows, p.data_compression, p.data_compression_desc, p.xml_compression, p.xml_compression_desc
UNION ALL
SELECT [t].[name] AS [Table_Name], 
       [i].[name] AS [Index_Name],  
       '-' AS "Total_Records",
       (8 * SUM(a.used_pages))/1024   AS 'Size_MB',
       8 * SUM(a.used_pages)    AS 'Size_KB',
       p.data_compression AS "Data_Compression",
       [p].[data_compression_desc] AS [Data_Compression_Desc],
       p.xml_compression AS "XML_Compression", 
       p.xml_compression_desc AS "XML_Compression_Desc"
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE [p].[index_id] > 1
AND t.name in ('Type_Your_Table_Name')
GROUP BY t.name, i.name, p.data_compression, p.data_compression_desc, p.xml_compression, p.xml_compression_desc