How do I select from the tables listed in INFORMATION_SCHEMA.TABLES in Snowflake?

Question

The information schema has a view called tables that lists the names of all the tables in the database. If all the the tables the same columns and I'd want to select table_name, value of certain column, how would I do that?

For example, I have TABLES

table_name	...
TABLE1	...
TABLE2	...
TABLE3	...

TABLE1

common_column	val
this_value	A
not_this_value	B
not_this_value	C

TABLE2

common_column	val
not_this_value	A
this_value	B
not_this_value	C

TABLE3

common_column	val
not_this_value	A
not_this_value	B
this_value	C

and I want an output of all the table_names with their value when common_column='this_value':

table_name	val
TABLE1	A
TABLE2	B
TABLE3	C

Thank you!

Answer 1

You could use RESULT_SCAN to achieve this (See https://docs.snowflake.com/en/sql-reference/functions/result_scan)

It is a bit unclear from your post what column you want to filter on.

Please try using the below queries and let me know if this helps: (you have to first run a select query from the information_schema.tables view in order to use its result in RESULT_SCAN)

select * from information_schema.tables;
select table_name,
case 
    WHEN column1 = 1 THEN 'A'
    WHEN column1 = 2 THEN 'B'
    ELSE 'C'
END AS val
from TABLE(RESULT_SCAN(LAST_QUERY_ID())) where common_column='this_value';

Answer 2

use the COLUMNS table:

select 
    table_catalog, 
    table_schema, 
    table_name
from information_schema.columns
where column_name = 'ID';