I needed to query SQL Servers metadata about partitioned tables, especially the column and the partition scheme used partitioning. The former is quite nicely documented in the SQL Server documentation (see link below), but the latter is not (yet). I have written the team about this, hopefully the documentation will be updated. Until then, I wrote this blog post to help others searching for an answer to this.
Information about partition schemes is are stored both in the sys.partition_schemes view and in the sys.data_spaces view. If a table is partitioned, the data_space_id attribute in the sys.indexes view now points to a partition scheme in the sys.data_spaces view.
The following query (modified only a tiny bit from docs) display the partition column and the partition scheme used for all partitioned tables in the database:
SELECT
t.[object_id] AS ObjectID
, t.name AS TableName
, ic.column_id AS PartitioningColumnID
, c.name AS PartitioningColumnName
, ps.name AS PartitioningSchemeName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] <= 1 -- clustered index or a heap
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column
JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
The query works both for on-prem SQL Server and for Azure SQL database.
Read more about table partitioning here:
Check out the awesome site with HTTP error codes and cat pictures here:
to include partition schemata used for cLustered columnstore tables, one may want to use AND i.[type] IN (0,1,5) -- clustered index or a heap or clustered columnstore