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
Best Answer
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:
If I create a few partitioned tables:
I get the following results: