Transact SQL – MetaData – List Object\Index Partition Columns

Introduction

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.

Help

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.

Code

dbo.ufn_ListObjectPartitionColumns

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

 
 

Use Cases

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
	)	
 ;	

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s