How do I list tables in Google BigQuery that match a certain name?

Question

I'm using template-suffix based tables in BigQuery. In order to actually take advantage of these, I need to scope my query to a specific table. Is there a way using legacy, or standard SQL to just list the tables that meet a specific pattern?

Answer 1

Check Metadata about tables in a dataset
You can do something like below in BigQuery Legacy SQL

SELECT * 
FROM publicdata:samples.__TABLES__
WHERE table_id CONTAINS 'github'

Or with BigQuery Standard SQL

SELECT * 
FROM publicdata.samples.__TABLES__
WHERE starts_with(table_id, 'github') 

I just realized that you most likely meant not How do I list tables that match a certain name? but rather How do I query tables that match a certain name?

In BigQuery Standard SQL - you can use _TABLE_SUFFIX and in BigQuery Legacy SQL you can use TABLE_DATE_RANGE() or TABLE_DATE_RANGE_STRICT() or TABLE_QUERY()

You should check Querying Multiple Tables Using a Wildcard Table for more details