How to get actual, specific column data types in Snowflake using SQL?

Question

I need to get column data types for multiple Snowflake tables and views, using SQL. SQL can be Snowflake-specific.

I know that this task can be accomplished by querying standard INFORMATION_SCHEMA objects. However, the column data types returned from such a query are generalized types like TEXT, NUMBER etc. I am trying to get actual Snowflake column data types, e.g. VARCHAR, DATETIME, SMALLINT etc.

In SQL Server, for example, this can be done by querying sys views. Is there an equivalent set of objects in Snowflake that can be queried? I haven’t been able to find any.

Additional constraint is that it should be a single query that returns column types for multiple tables, not one query per table or view.

Answer 1

If the COLUMNS-view (https://docs.snowflake.com/en/sql-reference/info-schema/columns.html) doesn't provide you enough information, you can try to use SHOW COLUMNS to list all columns from all tables for which you have access priviliges: https://docs.snowflake.com/en/sql-reference/sql/show-columns.html

According to the docs you can see at least presicion and scale of the data type there:

{"type":"FIXED","precision":38,"scale":0,"nullable":true}