Find All Compressed tables in database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Find All Compressed tables in database -...

Find All Compressed tables in database - script

Rate Topic

Display Mode

Topic Options

Author

Message

SQL Stud

Posted Monday, April 12, 2010 2:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:08 PM
Points: 154, Visits: 179

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

Wink

Post #901943

Garadin

Posted Monday, April 12, 2010 3:44 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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. Wink

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation

Post #902026

SQL Stud

Posted Monday, April 12, 2010 5:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 29, 2013 8:08 PM
Points: 154, Visits: 179

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

« Prev Topic | Next Topic »

Permissions