SQL Server: test # of partitions == partition_functions.fanout

Question

I am attempting to write a test for already partitioned schemas.

The test should confirm that each schema was partitioned # number of times where # = sys.partition_functions.fanout.

I have found the following code: (according to my understanding gives the number of partitions per schema)

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

I am currently attempting to understand how to connect each table to the partition function 'fanout' value.

Ideally I would like to be able to create a table with the following columns: table name, number of partitions, sys.partition_functions.fanout

Answer 1

Not sure why you want to do this, but some of the dmv links are a bit tricky to figure out from BOL. I believe the following meets your requirements:

SELECT t.name, MAX(pf.fanout) fanout, COUNT(*) cnt_partitions
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.[type] IN (0, 1, 5) -- heap, CI, or CCI
GROUP BY t.name;

If I create a few partitioned tables:

CREATE PARTITION FUNCTION functioning_part_function
(BIGINT)
AS RANGE LEFT
FOR VALUES (
  0
, 5000000
, 10000000
, 15000000
, 20000000
, 25000000
, 30000000
, 35000000
, 40000000
, 45000000
, 50000000
); 

CREATE PARTITION SCHEME scheming_part_scheme
AS PARTITION functioning_part_function
ALL TO ( [PRIMARY] );

CREATE TABLE dbo.PART_TABLE (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

CREATE TABLE dbo.PART_TABLE_2 (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

I get the following results:

╔══════════════╦════════╦════════════════╗
║     name     ║ fanout ║ cnt_partitions ║
╠══════════════╬════════╬════════════════╣
║ PART_TABLE   ║     12 ║             12 ║
║ PART_TABLE_2 ║     12 ║             12 ║
╚══════════════╩════════╩════════════════╝