Query table based on table_name from information_schema

[nilton]

Hi,

I have one table that changes the name every 60 days. The name simple increases the number version, for example:
* Firtst 60 days: table_name_v1. After 60 days: table_name_v2 and so on.

What i want is to query the table wich name returned in the query of information_schema. This is important because when the name change, I won't need to adapt the query name. 
Here is a example of what I want. 

CREATE OR REPLACE TEMP VIEW db_copart AS

SELECT

    table_name

FROM information_schema.tables

WHERE

    table_schema = 'table_sandbox'

    and table_name ILIKE '%db_pi_copart_v%'; -- this will return the latest table version

SELECT

        *

FROM table_sandbox.(select table_name from db_copart)

Thank you all for any help.

[radothede]

The simpliest way would be propably using spark.sql

%py
tbl_name = 'table_v1'
df = spark.sql(f'select * from {tbl_name}')
display(df)

 From there, You can simply create temporary view:

%py
df.createOrReplaceTempView('table_act')

and query it using SQL statements:

%sql
select * from table_act order by 1 asc;

[nilton]

Thank you @radothede , but in Databricks SQL I can only use SQL, as far as I know