Query DESCRIBE TABLE like a table

DESCRIBE TABLE is a powerful tool for understanding the structure of a table. However, in Snowflake the output of DESCRIBE TABLE is not treated like a regular table (i.e. can be manipulated using SELECT). To query DESCRIBE TABLE as a regular table we have to first run it, then run a SELECT query on the last query run which will be DESCRIBE TABLE.

Create Table Of Superheroes

-- Create a table called SUPERHEROES. If it already exists, replace it.
CREATE OR REPLACE TABLE SUPERHEROES (
  -- Column called ID allowing up to five characters
  "ID" VARCHAR(5), 
  -- Column called NAME allowing up to 100 characters
  "NAME" VARCHAR(100),
  -- Column called ALTER_EGO allowing up to 100 characters
  "ALTER_EGO" VARCHAR(100)
);

Insert Rows For Each Superhero

-- Insert rows into SUPERHEROES
INSERT INTO SUPERHEROES 
    -- With the values
    VALUES
    ('XF6K4', 'Chris Maki', 'The Bomber'),
    ('KD5SK', 'Donny Mav', 'Nuke Miner');

Describe The Table

-- Describe the table SUPERHEROES
DESCRIBE TABLE SUPERHEROES;

Run A Query On The Describe Query’s Output

-- Select the "name" and "type" columns
SELECT "name", "type" 
-- Of the last query (in this case the DESCRIBE TABLE query)
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
;
name type
ID VARCHAR(5)
NAME VARCHAR(100)
ALTER_EGO VARCHAR(100)