Quick one today:
How to get a list of objects (or indexes) that are compressed or have COMPRESSION enabled?
I frequently use the below script to get a complete list. Just modify it as needed to fit your specific need.
-- -- Get a list of indexes that have COMPRESSION enabled -- SELECT O.name AS [Table_Name] , O.object_id AS [Object_ID] , S.name AS [Schema_Name] , P.rows AS [Number_of_Rows] , P.data_compression_desc AS [Compression_Description] , I.name AS [Index_Name] , I.type_desc AS [Index_Type] , I.index_id AS [Index_ID] , D.name AS [FileGroup] FROM sys.partitions AS P INNER JOIN sys.objects AS O ON P.object_id = O.object_id INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id INNER JOIN sys.indexes AS I ON I.index_id = P.index_id AND I.object_id = O.object_id INNER JOIN sys.data_spaces AS D ON D.data_space_id = I.data_space_id WHERE O.type = 'U' -- only user objects AND P.data_compression > 0 -- only the ones with COMPRESSION enabled AND S.name NOT IN ('sys') -- exclude system objects ORDER BY O.name ASC , I.index_id ASC , D.name ASC GO
Hope this helps,
_Sqltimes
I love you