Here I am needing to write generic SQL Procedures that handle some Partitioning Management
As the SQL I am writing needs to be generic, I need to programmatically determine the Partitioning Column for each Object.
I really did not know where to start to goggled for help. I was stuck on stupid hoping to tie Partition Information to objects.
But, Google came up with something more pertinent to Indexes.
The function lists the columns that are our objects are partitioned on.
use master go if OBJECT_ID(N'dbo.ufn_ListObjectPartitionColumns',N'IF') is null begin execute('create function dbo.ufn_ListObjectPartitionColumns() returns TABLE AS RETURN ( select 1/0 as [undefined]); ') end go alter function dbo.ufn_ListObjectPartitionColumns() returns TABLE as return ( select distinct top 100 PERCENT tblObject.object_id as objectID , schema_name(tblObject.schema_id) as schemaName , tblObject.name as objectName , tblIndex.name as indexName , tblIndex.type_desc , tblColumn.[name] partitionColumn , tblIndexColumn.partition_ordinal as paritionOrdinal , case when tblIndex.is_primary_key = 1 then 'Yes' else 'No' end as [IsPK] from sys.index_columns tblIndexColumn inner join sys.columns tblColumn ON tblColumn.object_id = tblIndexColumn.object_id and tblColumn.column_id = tblIndexColumn.column_id inner join sys.indexes tblIndex on tblIndexColumn.object_id = tblIndex.object_id and tblIndexColumn.index_id = tblIndex.index_id inner join sys.objects tblObject on tblIndex.object_id = tblObject.object_id where (tblIndexColumn.partition_ordinal > 0) order by tblIndexColumn.partition_ordinal ); go
Find Objects that are partitioned across 2 more different columns
One ready use case in our environment is to find the same object partitioned across different root columns.
select * from dbo.ufn_ListObjectPartitionColumns() tblPartitionColumns where objectID in ( select tblList.objectID from ( select objectID , partitionColumn from dbo.ufn_ListObjectPartitionColumns() tblPartColumn group by objectID , partitionColumn ) tblList group by tblList.objectID having count(*) > 1 ) ;