Snowflake sql table name wildcard

Question

What is a good way to "select" from multiple tables at once when the list of tables is not known in advance in snowflake sql? Something that simulates

Select * from mytable*

which would fetch same results as

Select * from mytable_1
union
Select * from mytable_2 
...

I tried doing this in a multistep.

show tables like 'mytable%';
set mytablevar =
 (select  listagg("name", ' union ') table_
    from table(result_scan(last_query_id())))

The idea was to use the variable mytablevar to store the union of all tables in a subsequent query, but the variable size exceeded the size limit of 256 as the list of tables is quite large.

Gokhan Atil Gokhan Atil · Accepted Answer · 2020-10-24T11:02:46

Even if you do not hit 256 character limits, it will not help you to query all these tables. How will you use that session variable?

If you have multiple tables which have the same structure, and hold similar data that you need to query together, why are the data not in one big table? You can use Snowflake's clustering feature to distribute data based on a specific column to your micro-partitions.

https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html

Anyway, you may create a stored procedure which will create/replace a view.

https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#dynamically-creating-a-sql-statement

And then you can query that view:

CALL UPDATE_MY_VIEW( 'myview_name', 'table%' );
SELECT * FROM myview_name;

Snowflake sql table name wildcard

1 Answers