Find the Partitioning Key on an Existing Table with Partition_Ordinal

@SQLSista asked a fun question on Twitter recently about how to figure out the partitioning key on an existing table:

Bad Partitioned HEAP predates me. No clustered index, Primary Key on ID column and multiple datetime columns. How to find the partitioning key? #sqlhelp

Scripting the Table Doesn’t Show You The Partitioning Key

This seems easy, right? Nope, it’s not. If you just right click the table and say “Script to new window”, it does NOT script the partitioning scheme and column you chose as the partitioning key. Which is kind of a big bummer.

Before I ever saw the tweet, the magic of the #sqlhelp tag kicked in, and @MikeFal helped out:

You’ll want to look at sys.index_columns where partition_ordinal = 1. Since it’s a heap, index_id =0. #sqlhelp

Coincidentally, I wrote a script to show this exact thing the day before the question came up on Twitter. Here’s a script to save you some time if you ever need to do this yourself.

Show Me the Partitioning Key!

This script shows all partitioned tables in a database with the partition function, partition scheme, table name, index name, and all column names and properties.

Notes:

• The “partition_ordinal” column tells you if the column is the partitioning key. (0 = nope, 1 = yep)
• The query shows all indexes on partitioned tables, whether or not those indexes are partitioned (aka “aligned”) or not. This is important because non-aligned indexes on partitioned tables mean you can’t do partition switching.

with partitionedtables AS (
    SELECT DISTINCT 
        t.object_id,
        t.name AS table_name
    FROM sys.tables AS t
    JOIN sys.indexes AS si on t.object_id=si.object_id 
    JOIN sys.partition_schemes AS sc on si.data_space_id=sc.data_space_id
)
SELECT 
    pt.table_name,
    si.index_id,
    si.name AS index_name,
    ISNULL(pf.name, 'NonAligned') AS partition_function,
    ISNULL(sc.name, fg.name) AS partition_scheme_or_filegroup,
    ic.partition_ordinal, /* 0= not a partitioning column*/
    ic.key_ordinal,
    ic.is_included_column,
    c.name AS column_name,
    t.name AS data_type_name,
    c.is_identity,
    ic.is_descending_key,
    si.filter_definition
FROM partitionedtables AS pt
JOIN sys.indexes AS si on pt.object_id=si.object_id
JOIN sys.index_columns AS ic on si.object_id=ic.object_id
    and si.index_id=ic.index_id
JOIN sys.columns AS c on ic.object_id=c.object_id
    and ic.column_id=c.column_id
JOIN sys.types AS t on c.system_type_id=t.system_type_id
LEFT JOIN sys.partition_schemes AS sc on si.data_space_id=sc.data_space_id
LEFT JOIN sys.partition_functions AS pf on sc.function_id=pf.function_id
LEFT JOIN sys.filegroups as fg on si.data_space_id=fg.data_space_id
ORDER BY 1,2,3,4,5,6 DESC,7,8
GO

Here’s a sample of what the output looks like for a database with one partitioned tables– at least the most interesting columns. My partitioned table is using BirthYear, which is an int column, as the partitioning key. I currently have two indexes on the table.

PS: I tested and did make sure that the query works properly against a partitioned heap, as in the original Twitter question. The index_name column will just be null in that case.