Finding Enterprise Only Features in Your SQL Server 2008 Database

-- Look for Enterprise only features in the current database
SELECT feature_name, feature_id
FROM sys.dm_db_persisted_sku_features
ORDER BY feature_name;
The four possible results you can get in the feature_name column of the query are Compression, Partitioning, TransparentDataEncryption, ChangeCapture. Compression is for data compression, Partitioning is for table or index partitioning, TransparentDataCompression is for transparent data compression, while ChangeCapture is for change data capture.

If you get any rows back from the query, that means that you are using one of these Enterprise-only features in your database, and you will not be able to restore the database to a SQL Server instance that is running on a lower SKU (such as Standard Edition). To be more precise, the database will go through the full restore process, and then fail at the end (since it has no way of knowing that any of these features is being used until the restore is complete).

It would be nice if the DMV gave multiple rows of output, with an object_id for each occurrence of the feature for things like data compression and partitioning. In the meantime, the query below will help you find the tables that are using data compression.

-- Get Table names, row counts,
-- and compression status for clustered index (SQL 2008 Only)
SELECT OBJECT_NAME(object_id) AS [Table Name],
SUM(Rows) AS [Row Count], data_compression_desc AS [Compression]
FROM sys.partitions
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(object_id) NOT LIKE 'queue_%'
AND OBJECT_NAME(object_id) NOT LIKE 'filestream_tombstone%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC;