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: 2 days ago @ 9:18 AM
Points: 1,519, Visits: 4,086
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: Sunday, December 14, 2014 1:15 PM
Points: 4, Visits: 85
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
Posted Saturday, December 13, 2014 9:49 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:54 PM
Points: 136, Visits: 127
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))[table]
,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;
Post #1643995
Posted Monday, December 15, 2014 5:03 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1644284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse