title: Query all table data and index compression
body: <p>Does anyone happen to have a generic SQL statement that'll list all of the tables and indexes in a database, along with their current compression setting, for each partition?</p>
  
  <p>Thanks.</p>
  
  <p>EDIT: This is as far as I got in my attempt to query tables, but I'm not sure the join is correct (I'm getting duplicates, which seems to be caused by the presence of indexes)</p>
  
  <pre><code>SELECT [t].[name], [p].[partition_number], [p].[data_compression_desc]
  FROM [sys].[partitions] AS [p]
  INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
  </code></pre>