Wednesday, March 24, 2010

Selecting Partition Information for All Tables in a Database

I am working on reviewing the current partition information in one of our databases. I found it interesting that it was fairly difficult to get the information I wanted using the system information.

I wanted to…

1. Find all tables that are partitioned
2. See what partition function and scheme the table is using
3. See what column the table is partitioned on
4. Find out the record count of each partition

After doing some digging, here is what I came up with.

SELECT OBJECT_NAME(p.OBJECT_IDTableName,
        
c.name PartColumn,
        
ps.name PartScheme,
        
pf.name PartFunction
 
FROM sys.data_spaces  d JOIN
      
sys.indexes i JOIN
      
(SELECT DISTINCT OBJECT_ID
       
FROM sys.partitions
       
WHERE partition_number 1p
       
ON i.OBJECT_ID p.OBJECT_ID
       
ON d.data_space_id i.data_space_id
      
JOIN sys.partition_schemes ps ON d.data_space_id ps.data_space_id
      
JOIN sys.partition_functions pf ON ps.function_id pf.function_id
      
JOIN sys.index_columns ic ON i.index_id ic.index_id AND i.OBJECT_ID ic.OBJECT_ID
      
JOIN sys.columns c ON c.OBJECT_ID ic.OBJECT_ID AND c.column_id ic.column_id

WHERE i.index_id AND ic.partition_ordinal 

This query gives me some good information, but once I have the preceding information, I can also use it to dynamically create statements using the $PARTITION function to show me the number of records that exist in each partition.

 SELECT 'SELECT $PARTITION.' +  pf.name '(' c.name ') AS ' +
        
OBJECT_NAME(p.OBJECT_ID) + 'Partition, COUNT(*) AS [COUNT] FROM ' +
        
OBJECT_NAME(p.OBJECT_ID) + ' GROUP BY $PARTITION.' +
        
pf.name '(' c.name +
        
') ORDER BY [COUNT] DESC;'
      
--  ') ORDER BY ' + OBJECT_NAME(p.object_id) + 'Partition;'
 
FROM sys.data_spaces  d JOIN
      
sys.indexes i JOIN
      
(SELECT DISTINCT OBJECT_ID
       
FROM sys.partitions
       
WHERE partition_number 1p
       
ON i.OBJECT_ID p.OBJECT_ID
       
ON d.data_space_id i.data_space_id
      
JOIN sys.partition_schemes ps ON d.data_space_id ps.data_space_id
      
JOIN sys.partition_functions pf ON ps.function_id pf.function_id
      
JOIN sys.index_columns ic ON i.index_id ic.index_id AND i.OBJECT_ID ic.OBJECT_ID
      
JOIN sys.columns c ON c.OBJECT_ID ic.OBJECT_ID AND c.column_id ic.column_id

WHERE i.index_id AND ic.partition_ordinal 


I found it useful to order by the Count and the Partition Number, so all you have to do is uncomment whichever line you would like to see the results ordered by.

2 comments:

gurbir said...
This comment has been removed by the author.
gurbir said...

Hi
Thanks for posting such a helpful blog. It really helped me i am new to partition in the tables in Sql Server

Thanks