Snowflake: Is there a query that will return data type as I set it when creating Table? int to int, not int to number?

Question

e.g.

create table dbdt (
  ID int,
  Name varchar(255));

when I query:

  select * from dbName.information_schema.columns
  where table_schema = 'schemaName' and table_name = 'dbdt'

results:

COLUMN_NAME	DATA_TYPE
ID	NUMBER
NAME	TEXT

What do I need to change in query in order to return INT and VARCHAR(255) as I set it when creating table?

Answer 1

As far as I know, you can't get those values as they get normalized for future operations, including get_ddl.

One option would be to go to the query history to find and parse the create statement:

• https://docs.snowflake.com/en/sql-reference/account-usage/query_history.html (1 year)
• https://docs.snowflake.com/en/sql-reference/functions/query_history.html (7 days)

---

I tried to reproduce the question , and the results come empty because the underlying name of the table is in ALL-CAPS:

select * from information_schema.columns where table_name = 'DBDT'

The data types have been normalized too:

select column_name, ordinal_position, data_type, character_maximum_length, numeric_precision
from information_schema.columns 
where table_name = 'DBDT

get_schema is more permissive with the caps, but it still normalizes the types:

select get_ddl('table', 'dbdt')