Show partitions query is not working in Azure synapse analytics for delta table

Question

I have created a delta table using the following query

create table test_delta_partition(id int, created_time date) PARTITIONED BY (created_time) using delta
And insert some records in that table.

When I try to execute the show partitions query I face the below issue

can you pls give me any alternate approach list down partitions?

Thank you for the solutions.

Answer 1

I got the solution for this issue:

• First, we will get partitions columns using the below query:
  DESCRIBE DETAIL ${table_name}
• After getting the columns we can use the distinct query. The syntax is below:
  SELECT DISTINCT COLUMN1,COLUMN2,... from ${table_name}

After using the above SQL query we can get partition values from the table.

In our example:
first, we will do
describe detail test_delta_partition

So we get partition columns from the above query like below

After that, we can use the distinct query and we get partition values:

SELECT DISTINCT created_time from test_delta_partition;

Answer 2

What is the way of getting list of partitions of an external table without querying the table.