In SQL Server, you can use the sys.partition_range_values system catalog view to find out the boundary values used for a partitioned table.

However, you’d need to know the function_id of the partition function before you use that view.

But as always, you can run a join against a bunch of other tables to get the desired info.

sys.partition_range_values

First, let’s see what columns the sys.partition_range_values view returns.

 SELECT * FROM sys.partition_range_values;

Result:

+---------------+---------------+----------------+---------+
| function_id   | boundary_id   | parameter_id   | value   |
|---------------+---------------+----------------+---------|
| 65542         | 1             | 1              | -1      |
| 65542         | 2             | 1              | 100     |
| 65542         | 3             | 1              | 10000   |
+---------------+---------------+----------------+---------+

Fortunately for me, I’ve only got one partitioned table in this database, so I’m not bombarded with results.

As mentioned, this view returns the function_id, so we can use that to join a bunch of other tables, so that we can return data for a specific table.

In my case this is superfluous, seeing as there’s only one partitioned table, but let’s assume you’ve got a whole lot of partitioned heaps and indexes, and you just want to narrow it down.

Narrow it to a Table

Here’s an example of returning the boundary ranges for a specific table called Movies.

SELECT 
    p.partition_number,
    r.boundary_id, 
    r.value AS [Boundary Value]   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE i.type <= 1 AND t.name = 'Movies' 
ORDER BY p.partition_number ASC;

Result:

+--------------------+---------------+------------------+
| partition_number   | boundary_id   | Boundary Value   |
|--------------------+---------------+------------------|
| 1                  | 1             | -1               |
| 2                  | 2             | 100              |
| 3                  | 3             | 10000            |
| 4                  | NULL          | NULL             |
+--------------------+---------------+------------------+

Here it is again, but we expand the select list to include other info, such as the index name, the name of the partition function, etc.

 SELECT 
    t.name AS [Table], 
    i.name AS [Index], 
    p.partition_number,
    f.name,
    r.boundary_id, 
    r.value AS [Boundary Value]   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE i.type <= 1 AND t.name = 'Movies' 
ORDER BY p.partition_number ASC;

Result:

+---------+------------------------------+--------------------+-------------------------+---------------+------------------+
| Table   | Index                        | partition_number   | name                    | boundary_id   | Boundary Value   |
|---------+------------------------------+--------------------+-------------------------+---------------+------------------|
| Movies  | PK__Movies__4BD2941AD44D2FCF | 1                  | MoviesPartitionFunction | 1             | -1               |
| Movies  | PK__Movies__4BD2941AD44D2FCF | 2                  | MoviesPartitionFunction | 2             | 100              |
| Movies  | PK__Movies__4BD2941AD44D2FCF | 3                  | MoviesPartitionFunction | 3             | 10000            |
| Movies  | PK__Movies__4BD2941AD44D2FCF | 4                  | MoviesPartitionFunction | NULL          | NULL             |
+---------+------------------------------+--------------------+-------------------------+---------------+------------------+

Here’s that result again using vertical output (so you don’t have to scroll sideways):

-[ RECORD 1 ]-------------------------
Table            | Movies
Index            | PK__Movies__4BD2941AD44D2FCF
partition_number | 1
name             | MoviesPartitionFunction
boundary_id      | 1
Boundary Value   | -1
-[ RECORD 2 ]-------------------------
Table            | Movies
Index            | PK__Movies__4BD2941AD44D2FCF
partition_number | 2
name             | MoviesPartitionFunction
boundary_id      | 2
Boundary Value   | 100
-[ RECORD 3 ]-------------------------
Table            | Movies
Index            | PK__Movies__4BD2941AD44D2FCF
partition_number | 3
name             | MoviesPartitionFunction
boundary_id      | 3
Boundary Value   | 10000
-[ RECORD 4 ]-------------------------
Table            | Movies
Index            | PK__Movies__4BD2941AD44D2FCF
partition_number | 4
name             | MoviesPartitionFunction
boundary_id      | NULL
Boundary Value   | NULL