Sql Server : Query all the indexes or tables that are COMPRESSED
- Posted by Sqltimes
- On July 19, 2014
- 0 Comments
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
---- 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 PINNER JOIN sys.objects AS O ON P.object_id = O.object_idINNER JOIN sys.schemas AS S ON S.schema_id = O.schema_idINNER JOIN sys.indexes AS I ON I.index_id = P.index_id AND I.object_id = O.object_idINNER JOIN sys.data_spaces AS D ON D.data_space_id = I.data_space_idWHERE O.type = 'U' -- only user objectsAND P.data_compression > 0 -- only the ones with COMPRESSION enabledAND S.name NOT IN ('sys') -- exclude system objectsORDER BY O.name ASC , I.index_id ASC , D.name ASCGO |
Hope this helps,
_Sqltimes
0 Comments