A fun question came up on Twitter recently:
— SQLSista (@SQLSista) March 10, 2016
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:
— Mike Fal (@Mike_Fal) March 10, 2016
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.
- 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.