Querying table partition

Question

Hi

I am writing a query to check that all of our table partitions are set up correctly.

I am pulling the scheme and function which is being used on a table and the range which will cause the table to split into the different filegroups.

But I haven't found a system view which will give a list of filegroups which are linked to the scheme

My query currently looks like this

Select ps.Name PartitionScheme, pf.name PartitionFunction, sv.value SplitValue

from sys.indexes I

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

join sys.partition_range_values sv on pf.function_id = sv.function_id

where i.object_id = object_id('tableName')

Thanks for any help



Answer 1

The query below will show the partitions, boundaries, filegroups and space information for the table and each index.

SELECT
	  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) AS ObjectName
	, CASE WHEN i.name IS NULL THEN '(heap)' ELSE i.name END AS IndexName
	, i.index_id AS IndexID
	, CASE WHEN pf.name IS NULL THEN '' ELSE pf.name END AS PartitionFunctionName
	, CASE pf.boundary_value_on_right WHEN 0 THEN 'LEFT' WHEN 1 THEN 'RIGHT' ELSE '' END AS PartitionFunctionRange
	, (SELECT name FROM sys.columns AS c WHERE c.object_id = ic.object_id AND c.column_id = ic.column_id) AS PartitioningColumn
	, CASE WHEN pf.name IS NULL THEN '' ELSE (SELECT
			CAST(ic.column_id AS varchar(11)) --partitioning column
			+ pf.type --partition function range specification (LEFT or RIGHT)
			 + (
				SELECT prv.value AS v
				FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id
				 ORDER BY v
				 FOR XML PATH('')
				 ) --non-null partition boundaries
			 + (
				SELECT 'N'
				FROM sys.partition_range_values AS prv WHERE ps.function_id = prv.function_id AND prv.value IS NULL
				) --NULL partition boundary, if present
				  AS PartitionFunctionDefinition
			FROM sys.indexes AS i
			INNER JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1
			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
			WHERE 
				i.object_id = p.object_id
				AND i.index_id = p.index_id
				) END AS PartitionFunctionDefinition
	, CASE WHEN pf.name IS NOT NULL THEN ds.name ELSE NULL END AS PartitionSchemeName
	, p.partition_number AS PartitionNumber
	, COUNT(*) OVER(PARTITION BY p.object_id, p.index_id) AS PartitionCount
	, fg.name AS FileGroupName
	, prv_left.value AS LowerBoundaryValue
	, prv_right.value AS UpperBoundaryValue
	, SUM(CASE WHEN p.index_id IN(0,1) THEN p.rows ELSE 0 END) OVER(PARTITION BY p.object_id, p.index_id) AS TableRowCount
	, p.rows AS Rows
	, pst.used_page_count AS UsedPages
	, pst.used_page_count * 8 AS UsedSpaceKB
	, pst.reserved_page_count as ReservedPages
	, pst.reserved_page_count * 8 as ReservedSpaceKB
FROM
	sys.partitions AS p 
	JOIN sys.dm_db_partition_stats AS pst ON pst.partition_id = p.partition_id AND pst.object_id = p.object_id AND pst.index_id = p.index_id
	JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
	JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
	LEFT JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id
	LEFT JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id
	LEFT JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number
	LEFT JOIN sys.filegroups AS fg ON fg.data_space_id = COALESCE(dds2.data_space_id, i.data_space_id)
	LEFT JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal = 1
	LEFT 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 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(N'YourSchema.YourTable')
ORDER BY
	ObjectName, 
	IndexID, 
	PartitionNumber;

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 2

Scriot written by Itzik Ben-Gan

- Definition of UDF fn_script_partition_scheme
IF OBJECT_ID('dbo.fn_script_partition_scheme', 'FN') IS NOT NULL
  DROP FUNCTION dbo.fn_script_partition_scheme;
GO
CREATE FUNCTION dbo.fn_script_partition_scheme(@ps AS SYSNAME)
  RETURNS NVARCHAR(4000)
AS
BEGIN

RETURN

N'CREATE PARTITION SCHEME ' + QUOTENAME(@ps) + N' AS PARTITION '
+
-- Return partition function name
-- based on input partition scheme name
(SELECT QUOTENAME(PF.name)
 FROM sys.partition_schemes AS PS
   JOIN sys.partition_functions AS PF
     ON PS.function_id = PF.function_id
 where PS.name = @ps)
+ 
N'
TO ('
+
-- Return concatenated list of filegroup names
-- that participate in partition scheme
STUFF(
  (SELECT N',' + QUOTENAME(DS.name) AS [text()]
   FROM sys.partition_schemes AS PS
     JOIN sys.destination_data_spaces AS DDS
       ON PS.data_space_id = DDS.partition_scheme_id
     JOIN sys.data_spaces AS DS
       ON DDS.data_space_id = DS.data_space_id
   WHERE PS.name = @ps
   ORDER BY DDS.destination_id
   FOR XML PATH(')), 1, 1, N')
+
N');';

PRINT dbo.fn_script_partition_scheme(N'PS1');
PRINT dbo.fn_script_partition_scheme(N'PS2');
And you should get the following output:

CREATE PARTITION SCHEME [PS1] AS PARTITION [PF1]
TO ([FG1],[FG2],[FG3],[FG4]);
CREATE PARTITION SCHEME [PS2] AS PARTITION [PF2]
TO ([FG5],[FG6],[FG7]);

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence