Data Warehousing and Data Science

11 December 2009

Find which partitioning function is used by a table

Filed under: SQL Server — Vincent Rainardi @ 8:10 pm

It’s a bit long winded:

select distinct from sys.tables t
join sys.partitions p on p.object_id = t.object_id
join sys.allocation_units au on au.container_id = p.partition_id
join sys.destination_data_spaces dds on dds.data_space_id = au.data_space_id
join sys.partition_schemes ps on ps.data_space_id = dds.partition_scheme_id
join sys.partition_functions pf on pf.function_id = ps.function_id
where = ‘table1’

The core of it is destination_data_spaces. This catalog view shows which partition scheme is used by each file group (data space). The file group links to partitions, then to the table. The partition scheme links to the partition function. Here it is, step by step:

  1. First we find out the object_id in sys.table:
    select * from sys.tables where name = ‘table1’
  2. Then we find the partition_id in sys.partition:
    select partition_id from sys.partitions where object_id = 12345678
    This results in many rows because the table has many partitions. Just take 1:
    select partition_id from sys.partitions where object_id = 12345678 and partition_number = 1 and index_id = 0
  3. Then we find the data_space_id in sys.allocation_unit (the column is called container_id):
    select data_space_id from sys.allocation_units where container_id = 987654321
  4. Then we find the partition_scheme_id in sys.destination_data_spaces:
    select partition_scheme_id from sys.destination_data_spaces where data_space_id = 33
  5. And finally we find the partition_function name in sys.partition_functions:
    select * from sys.partition_functions where function_id = 56789
  6. We can also find the boundary values if we want:
    select * from sys.partition_range_values where function_id = 56789

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: