CODEWITHSUNDEEP
sqlgoogle-bigquery
J. Ayo
J. Ayo

Reputation: 560

Using the table_id from __TABLES_SUMMARY__ in an SQL query to select tables

I currently use the following legacy SQL query to check how many rows are returned in each table within a data set each day:

SELECT Date, table1, table2
FROM
(SELECT RowDate AS Date, Count(RowDate) AS table1
FROM [project:dataset.table1] GROUP BY Date),
(SELECT RowDate AS Date, Count(RowDate) AS table2
FROM [project:dataset.table2] GROUP BY Date)

This works fine, except that I would like the query to SELECT all tables within a given data set. I believe there should be a way to use the table_id column from this query to extract the table names within a data set:

SELECT * FROM `project.dataset.__TABLES_SUMMARY__`

This gives me a table with meta data about all the tables within a dataset, including the table_id

The keen eyed will see that the second query is standard SQL, but I don't know the equivalent for Legacy (which I prefer to use)?

Can anyone provide a way to use the table_id in the first query and SELECT all tables (and name the Count(RowDate) column accordingly also)?

Thank you

Upvotes: 0

Views: 957

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

I don't know the equivalent for Legacy (which I prefer to use)? 

SELECT * FROM [project:dataset.__TABLES_SUMMARY__]

Upvotes: 3

Related Questions