Search code examples
azure-synapse

Get column names of temp table in Azure Synapse DW

This question offers the canonical way of doing this in SQL Server, but it doesn't work in Azure Synapse.

Because it is a temp table, I cannot look in INFORMATION_SCHEMA, and I can't query tempdb or tempdb.columns like one might normally.

I'd like a query that yields the column stack from #test

IF OBJECT_ID('tempdb..#test') IS NOT NULL
BEGIN
    DROP TABLE #test
END;

CREATE TABLE #test
WITH (DISTRIBUTION=ROUND_ROBIN,HEAP)
AS (
SELECT 'overflow' as stack
);

SELECT * FROM #test
Solution

  • You can't, sorry.

    The only workable "solution" is to CTAS a row to a table outside tempdb, then inspect its columns from sys.columns or information_schema.