Identifying the columns in temp table in synapse

Nandan Hegde 32,336 MVP

Hello All,
How can we get the list of all columns present in a temp table in Synapse?

IF OBJECT_ID(N'tempdb..#LocalCustomer') IS NOT NULL
BEGIN
Drop table  #LocalCustomer
Go

CREATE TABLE ajs.#LocalCustomer
(
 CustomerId int,
 CustomerName varchar(50))

I tried the below syntax to get the column details :

select * from tempdb.sys.columns where object_id =
object_id('tempdb..#localcustomer');

But I am getting the below error:
Msg 208, Level 16, State 1, Line 11
Invalid object name 'tempdb.pdw.columns'.

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-02T14:38:48.3+00:00

Hi @Nandan Hegde ,

Thanks for posting the query.

I have tried and observed the same, was not able to find any option. I have reached out to Product team to get more details on the same. I will get back to you once I hear from them.

Thanks for your patience!
HarithaMaddi-MSFT 10,136 Reputation points

2020-09-03T04:46:56.023+00:00

Hi @Nandan Hegde ,

I reached out to the internal team and they are looking into the possible options for it. Also, they would like to understand the scenario which requires looking at the columns list of temp tables to suggest a workaround if it is not possible in Synapse. Can you please share these details to help in further discussions.
HarithaMaddi-MSFT 10,136 Reputation points

2020-09-04T07:29:55.207+00:00

Hi @Nandan Hegde ,

We did not get a response from you. Can you please share more details as requested above to provide an alternate solution for the requirement as product team is still looking into possible commands to get these details. If you have found a solution, please share it with the community as it will benefit other members.

Thanks for your patience!
Nandan Hegde 32,336 Reputation points MVP

2020-09-08T03:37:36.583+00:00

Hello Haritha,
We are using a tool DBT(data build tool) wherein everything is created via temp tables dynamically.
And via the temp tables, SCD or snapshot logics are executed at which the tool need to read the column schema of the temp tables to map it to the destination tables while data insertion.

Hope this clarifies the queries.
HarithaMaddi-MSFT 10,136 Reputation points

2020-09-08T15:10:29.87+00:00

Thanks @Nandan Hegde for sharing more details. Product team confirmed that there is no possibility to retrieve this information in Synapse. Please suggest if there is any alternative to store the metadata in DBT while it is getting created without retrieving from Synapse. I would recommend you to upvote the similar idea at the feedback forum. All the feedback you share, is closely monitored by the Synapse Product team and implemented in future releases.

Apologies for the inconvenience!

Share via

Identifying the columns in temp table in synapse

Your answer