Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find All Compressed tables in database - script Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 2:09 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:32 AM
Points: 155, Visits: 184
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


Post #901943
Posted Monday, April 12, 2010 3:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 1,519, Visits: 4,082
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?

Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #902026
Posted Monday, April 12, 2010 5:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 6:32 AM
Points: 155, Visits: 184
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.
Post #902073
Posted Thursday, July 4, 2013 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 4, Visits: 77
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
Post #1470443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse