CODEWITHSUNDEEP
snowflake-cloud-data-platform
Mark Bluemer
Mark Bluemer

Reputation: 1

Identify if a column is Virtual in Snowflake without SHOW COLUMNS

Currently we're identifying if a column is virtual in Snowflake by running a SHOW COLUMN query and checking the KIND field for VIRTUAL_COLUMN. Unfortunately, there's a 10k limit on entries returned from SHOW queries in Snowflake and we'd like to be able to run this query at the schema level on schemas ~25k tables.

According to this post there's no way to identify virtual columns in the information_schema.columns view and we'd like to avoid having to run a SHOW COLUMNS query at the table level or having to run a desc table on every table.

Is there some other way we can identify virtual columns at scale?

Upvotes: 0

Views: 85

Answers (1)

Jim Demitriou
Jim Demitriou

Reputation: 603

Unfortunately, not aware of any native capability. I would consider writing a script using the get_ddl() function and run it against all objects in a schema.

Upvotes: 1

Related Questions