Get a List of Tables That Are Compressed

Modern versions of SQL Server have the option for row or page level compression.

Here is the script to find those tables that have compression enabled:

SELECT DISTINCT t.name AS TableName, p.data_compression_desc
  FROM sys.partitions p
 INNER JOIN sys.tables t ON p.object_id = t.object_id
 WHERE p.data_compression > 0;

Tested on SQL Server 2019.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!