How to get list of all tables/index that are not compressed for all Databases in an instance

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 1

I found this query but I only need tables/indexes bigger than below requirement

Any table with data > 800 MB not compressed

Any index with disk usage > 80 MB not compressed

Any idea, how to modify the script to get that?

SELECT DISTINCT

SERVERPROPERTY('servername') [instance]

,DB_NAME() [database]

,QUOTENAME(OBJECT_SCHEMA_NAME(sp.object_id)) +'.'+QUOTENAME(Object_name(sp.object_id))

,ix.name [index_name]

,sp.data_compression

,sp.data_compression_desc

FROM sys.partitions SP

LEFT OUTER JOIN sys.indexes IX

ON sp.object_id = ix.object_id

and sp.index_id = ix.index_id

WHERE sp.data_compression <> 1

ORDER BY 2;

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

Don't have time right now to fully test/refine this, but it should be close at least:

SELECT DISTINCT

SERVERPROPERTY('servername') [instance]

,DB_NAME() [database]

,QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +'.'+QUOTENAME(Object_name(p.object_id))

,p.data_compression

,p.data_compression_desc

FROM sys.partitions p

INNER JOIN (

SELECT container_id, SUM(total_pages) AS total_pages, SUM(used_pages) AS used_pages

FROM sys.allocation_units

WHERE type IN (1, 3)

GROUP BY container_id

) AS au ON au.container_id = p.partition_id

WHERE p.object_id > 100 AND

((p.index_id = 0 AND used_pages >= 800 * 128) OR

(p.index_id <> 0 AND used_pages >= 80 * 128))

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 3

yes, this is good, thanks Scott.

Regards,
SQLisAwe5oMe.