How To See Partition Details In Partitioned Table On SQL Server

dbtut August 15, 2018 MSSQL

I explained how to create a partition int the article named “How To Create Partition On SQL Server“.

In this article I will share the script that will show the details of partitions of a partitioned tables.

First, you can see the partitioned tables in the database with the help of the following script.

SELECT DISTINCT t.name
FROM sys.partitions p
INNER JOIN sys.tables t
ON p.object_id = t.object_id
WHERE p.partition_number <> 1

SELECT DISTINCT t.name

FROM sys.partitions p

INNER JOIN sys.tables t

ON p.object_id = t.object_id

WHERE p.partition_number <> 1

Then you can see partition details on partitioned tables with the help of the following script.

In the query, you should type the name of the table you want to see the partition detail in the WHERE block, and the name of the database in the USE section.

USE [your_database_name]
GO
SELECT PartitionScheme AS [Partition Scheme Name],
       PartitionFunction AS [Partition Function Name],
          FileGroupName AS [File Group Name],
          rows AS [Record Count],
          CAST(SUM(CAST(sf.size AS BIGINT))/131072.0 AS DECIMAL(19,2)) AS [Size GB],
          PartitionFunctionValue AS [Partition Function Value]
FROM
(select distinct ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName,
 p.rows, prv.value AS PartitionFunctionValue,fg.data_space_id
    from sys.indexes i 
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id 
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id 
    join sys.partition_functions pf on pf.function_id = ps.function_id 
    left join sys.partition_range_values prv on prv.function_id = pf.function_id AND prv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id  
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id 
    where i.object_id = object_id('Partitioned Table Name')) a
join sys.sysfiles sf ON a.data_space_id=sf.groupid
GROUP BY PartitionScheme,PartitionFunction,FileGroupName,rows,PartitionFunctionValue

USE [your_database_name]

SELECT PartitionScheme AS [Partition Scheme Name],

PartitionFunction AS [Partition Function Name],

FileGroupName AS [File Group Name],

rows AS [Record Count],

CAST(SUM(CAST(sf.size AS BIGINT))/131072.0 AS DECIMAL(19,2)) AS [Size GB],

PartitionFunctionValue AS [Partition Function Value]

FROM

(select distinct ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName,

p.rows, prv.value AS PartitionFunctionValue,fg.data_space_id

from sys.indexes i

join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id

join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

join sys.partition_functions pf on pf.function_id = ps.function_id

left join sys.partition_range_values prv on prv.function_id = pf.function_id AND prv.boundary_id = p.partition_number

join sys.allocation_units au ON au.container_id = p.hobt_id

join sys.filegroups fg ON fg.data_space_id = au.data_space_id

where i.object_id = object_id('Partitioned Table Name')) a

join sys.sysfiles sf ON a.data_space_id=sf.groupid

GROUP BY PartitionScheme,PartitionFunction,FileGroupName,rows,PartitionFunctionValue

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To See Partition Details In Partitioned Table On SQL Server

About dbtut

3 comments

Leave a Reply Cancel reply