April 12, 2010 at 2:09 pm
So here is what I have so far, seems to work but I swear i've seen a more simplistic way to do this. What I want to do is display all tables in a database that are using some sort of compression (regardless if it's PAGE or ROW). Below is what I already have, but if anyone knows of anything better I would be greatful.
--script to identify compressed tables
SELECT st.name, st.object_id, sp.partition_id, sp.partition_number, sp.data_compression,
sp.data_compression_desc FROM sys.partitions SP
INNER JOIN sys.tables ST ON
st.object_id = sp.object_id
WHERE data_compression <> 0
😉
April 12, 2010 at 3:44 pm
What do you mean by "better"? Does this have the information you need, or are you looking for something more? Like, perhaps, the compression settings of the indexes as well?
April 12, 2010 at 5:45 pm
It's got all the info I need, but thanks for reminding me to add indexes in there.
I was actually thinking I've seen a DMV or system view that has this information already in one neat little place, but I haven't been able to find one.
July 4, 2013 at 7:50 am
extended for index name
SELECT st.name, ix.name , st.object_id, sp.partition_id, sp.partition_number, sp.data_compression,sp.data_compression_desc
FROM sys.partitions SP
INNER JOIN sys.tables ST ON st.object_id = sp.object_id
LEFT OUTER JOIN sys.indexes IX ON sp.object_id = ix.object_id and sp.index_id = ix.index_id
WHERE sp.data_compression <> 0
order by st.name, sp.index_id
December 13, 2014 at 9:49 pm
Huge help! I played with it some. I took the reference sys.tables out and removed some of the columns. Thanks!
SET NOCOUNT ON
GO
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 <> 0
ORDER BY 2;
December 15, 2014 at 5:03 pm
FYI:
WHERE sp.data_compression > 0
is more sargable then "<> 0"; avoid <> unless you have to use it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply