Find the Partitioning Key on an Existing Table with Partition_Ordinal

A fun question came up on Twitter recently:

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:

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!

[iframe src=”http://giphy.com/embed/gSRkSblDEjUuk” width=”500″ height=”275″ frameBorder=”0″ webkitAllowFullScreen mozallowfullscreen allowFullScreen][/iframe]

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.

Click to see larger image

Click to see larger image

 

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.

 

 

 

 

Previous Post
The Case of DATETIME2 and Partition Elimination
Next Post
Live Query Statistics Don’t Replace Actual Execution Plans

Related Posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu