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;

Answer 2

Use this to also see the other distribution types other than hash.

SELECT OBJECT_SCHEMA_NAME(PDW_TABLE_DISTRIBUTION_PROPERTIES.OBJECT_ID) AS [SCHEMA],
       OBJECT_NAME(PDW_TABLE_DISTRIBUTION_PROPERTIES.OBJECT_ID) AS [TABLE],
       DISTRIBUTION_COLUMN.DISTRIBUTION_ORDINAL AS [DISTRIBUTION_ORDINAL],
       PDW_TABLE_DISTRIBUTION_PROPERTIES.DISTRIBUTION_POLICY_DESC AS [DISTRIBUTION_POLICY],
       DISTRIBUTION_COLUMN.[COLUMN]
FROM SYS.PDW_TABLE_DISTRIBUTION_PROPERTIES
  LEFT OUTER JOIN ( 
            SELECT PDW_COLUMN_DISTRIBUTION_PROPERTIES.OBJECT_ID AS [OBJECT_ID],
                   PDW_COLUMN_DISTRIBUTION_PROPERTIES.DISTRIBUTION_ORDINAL AS [DISTRIBUTION_ORDINAL],
                   COLUMNS.NAME AS [COLUMN]
            FROM SYS.PDW_COLUMN_DISTRIBUTION_PROPERTIES
              INNER JOIN SYS.COLUMNS
                      ON COLUMNS.OBJECT_ID = PDW_COLUMN_DISTRIBUTION_PROPERTIES.OBJECT_ID
                     AND COLUMNS.COLUMN_ID = PDW_COLUMN_DISTRIBUTION_PROPERTIES.COLUMN_ID
            WHERE DISTRIBUTION_ORDINAL > 0 ) AS DISTRIBUTION_COLUMN
               ON DISTRIBUTION_COLUMN.OBJECT_ID = PDW_TABLE_DISTRIBUTION_PROPERTIES.OBJECT_ID
ORDER BY OBJECT_SCHEMA_NAME(PDW_TABLE_DISTRIBUTION_PROPERTIES.OBJECT_ID),
         OBJECT_NAME(PDW_TABLE_DISTRIBUTION_PROPERTIES.OBJECT_ID),
         DISTRIBUTION_COLUMN.DISTRIBUTION_ORDINAL