Get Partition Info

If you are not sure how much data is in your partitioned table buckets or in doubt if there will be any data movement and want to check to be sure the lower or upper boundary is empty before a split or merge you could use the table function below to quickly determine that part. 

I think there could be many other ways this function could be used like maybe in a SQL Job to monitor the buckets, set some alerts to check upper/lower boundary based on it to prevent accidental data movement while splitting or merging, ETC. It can be easily changed to an IN list rather than just a single table or just use it as is via the (undocumented) sp_MSforeachtable to get build/retrieve a report for all partitioned tables in a SQL database and many more. 

For sure this same thing would be possible to be done via a quick search on the Internet and find a similar query or maybe even Microsoft would add a DM view to do the same however until that time I use the function below on SQL 2008, 2012, and 2014  servers and I believe it should work in 2016 as well.

In order to use it please add the following function to the database where the partitioned table(s) is/are:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[tf_GetPartitionInfo] (@TableName sysname)
RETURNS TABLE 
AS RETURN
(
SELECT TOP 100 PERCENT
OBJECT_NAME(p.object_id) AS TableName, 
i.name AS IndexName, 
    p.index_id AS IndexID, 
ds.name AS PartitionScheme, 
pf.name AS PartitionName,
p.partition_number AS PartitionNumber, 
fg.name AS FileGroupName, 
prv_left.value AS LowerBoundaryValue, 
prv_right.value AS UpperBoundaryValue, 
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS PartitionFunctionRange, 
p.rows AS Rows
FROM
sys.partitions AS p INNER JOIN
sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN
sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id INNER JOIN
sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id INNER JOIN
sys.partition_functions AS pf ON pf.function_id = ps.function_id INNER JOIN
sys.destination_data_spaces AS dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number INNER JOIN
sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id LEFT OUTER JOIN
sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT OUTER JOIN
sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID(@TableName) and p.index_id = 1
ORDER BY PartitionNumber
);

After the function was added to the SQL database you could run a select like below against the database where the partitioned table(s) is/are - just change MyTableName with the actual partitioned table name to get the results:

select TableName,PartitionScheme,PartitionName,PartitionNumber,FileGroupName,LowerBoundaryValue,UpperBoundaryValue,[Rows] 
from dbo.tf_GetPartitionInfo ('MyTableName') order by PartitionNumber;

Hope you found this information useful.