How do I find the column used in a HASH DISTRIBUTION within Azure Synapse?

Question

This query gives me everything i need except the column used in HASH distribution:

select * from sys.pdw_table_distribution_properties

Do I need a table to join to get it?

Answer 1

You have to specify the distribution as HASH in sys.pdw_table_distribution_properties and set the distribution_ordinal to > 0 (presumably paving the way for multi-column HASH tables). Something like this should work:

SELECT
    OBJECT_SCHEMA_NAME(tdp.object_id) schemaName,
    OBJECT_NAME(tdp.object_id) tableName,
    c.name AS hashDistributionColumnName,
    cdp.distribution_ordinal
FROM sys.pdw_table_distribution_properties tdp
        INNER JOIN sys.pdw_column_distribution_properties cdp ON tdp.object_id = cdp.object_id
            INNER JOIN sys.columns c ON cdp.object_id = c.object_id
                AND cdp.column_id = c.column_id
WHERE tdp.distribution_policy_desc = 'HASH'
  AND cdp.distribution_ordinal > 0;