sqlsnowflake-cloud-data-platformcommon-table-expression

show columns in CTE returns an error - why?

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?

Solution

  • 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;