Query all table data and index compression

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.

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