Query all tables within a Snowflake Schema

1
votes

Due to the way our database is stored, we have tables for each significant event that occurs within a products life:

  • Acquired
  • Sold
  • Delivered

I need to go through and find the status of a product at any given time. In order to do so I'd need to query all of the tables within the schema and find the record with the most up to date record. I know this is possible by union-ing all tables and then finding the MAX timestamp but I wonder if there's a more elegant solution?

Is it possible to query all tables by just querying the root schema or database? Is there a way to loop through all tables within the schema and substitute that into the FROM clause?

Any help is appreciated.

Thanks

1
sqlsnowflake-cloud-data-platform

1 Answers

1
votes

You could write a Stored Procedure but, IMO, that would only be worth the effort (and more elegant) if the list of tables changed regularly. If the list of tables is relatively fixed then creating a UNION statement is probably the most elegant solution and relatively trivial to create - if you plan to use it regularly then just create it as a View.

The way I always approach this type of problem (creating the same SQL for multiple tables) is to dump the list of tables out into Excel, generate the SQL statement for the first table using functions, copy this function down for all the table names and then concatenate all these statements in a final function. You can then just paste this text back into your SQL editor