show columns in CTE returns an error

I have a show columns query that works fine:

SHOW COLUMNS IN table

but it fails when trying to put it in a CTE, like this:

WITH columns_table AS (
SHOW COLUMNS IN table
)

SELECT * from columns_table

any ideas why and how to fix it?

CodePudding user response：

Using RESULT_SCAN:

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table. This is particularly useful if you want to process the output from any of the following:

SHOW or DESC[RIBE] command that you executed.

SHOW COLUMNS IN ...;

WITH columns_table AS (
   SELECT *
   FROM table(RESULT_SCAN(LAST_QUERY_ID()))
)
SELECT *
FROM columns_table;

CodePudding user response：

CTE requires select clause and we cannot use SHOW COLUMN IN CTE's and as a alterative use INFORMATION_SCHEMA to retrieve metadata .Like below:

WITH columns_table AS (
Select * from INTL_DB.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='CURRENCIES'
)
SELECT * from columns_table;