List all columns with datatype from a specific table in Snowflake

Question

I tried the answers in this very similar question: List all columns wtih datatype in specific table in Snowflake and they work in the Snowflake WebUI but not in Teradata SQL Assistant.

But even using SHOW COLUMNS IN TABLE MYSCHEMA.MYTABLE; doesn't give me exactly what I need. I want the query to return information like this:

Columns     Data Type
REGION_ID   NUMBER(38,0)
STORE_NAME  VARCHAR(20)
VALID_NAME  VARCHAR(1)

The data_type column from SHOW COLUMNS IN TABLE MYSCHEMA.MYTABLE; doesn't look like the above grid which is from the Snowflake WebUI ("View Details" command by right clicking the table name in left hand side of the "Find database objects" section).

The view from SHOW COLUMNS... looks like this:

data_type
{"type":"FIXED","precision":38,"scale":0,"nullable":true}
{"type":"TEXT","length":20,"byteLength":80,"nullable":true,"fixed":false}
{"type":"TEXT","length":1,"byteLength":4,"nullable":true,"fixed":false}

Yes, similar but I'm looking for something equivalent to the grid above or the Teradata SHOW TABLE command.

Answer 1

See Snowflake's GET_DDL() function.

If we create a schema and table like this:

CREATE SCHEMA MYSCHEMA
;
CREATE TABLE MYSCHEMA.MYTABLE (
  REGION_ID   NUMBER(38,0)
 ,STORE_NAME  VARCHAR(20)
 ,VALID_NAME  VARCHAR(1)
)
;

and then execute GET_DDL() like this:

SELECT GET_DDL('TABLE', 'MYSCHEMA.MYTABLE')
;

we will get output like this:

create or replace TABLE MYTABLE (
    REGION_ID NUMBER(38,0),
    STORE_NAME VARCHAR(20),
    VALID_NAME VARCHAR(1)
);

Answer 2

Can you run both these queries at the same time in Teradata SQL Assistant

show columns ;
SELECT "column_name" Columns,parse_json("data_type"):type::string Datatype FROM (select * from table(result_scan(last_query_id()))) ;

Answer 3

Modifying @rajib deb

show columns;
SELECT "column_name" Columns
,case when parse_json("data_type"):type::string = 'FIXED' then 'NUMBER' || '(' || parse_json("data_type"):precision::string  || ',' || parse_json("data_type"):scale::string || ')'
when parse_json("data_type"):type::string = 'TEXT' then 'VARCHAR' || '(' || parse_json("data_type"):length::string || ')'
end as Data_type
FROM (select * from table(result_scan(last_query_id()))) ;