Query each table in a column in big query

March 24, 2023

I need to "touch" all of our materialized views in a big query dataset periodically for a business use case. I was hoping to be able to gather all of the table names in a SELECT statement, and then do a "FOR EACH" type of loop through each view name, but it seems like BQ will not allow this. Is there a better way to do what I am attempting below?

FOR record IN
  (SELECT CONCAT('project.dataset.', table_name) as full_name from `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'MATERIALIZED VIEW')
DO
  SELECT 1 FROM record.full_name;
END FOR;

This query is supposed to run against the information schema to find all of the materialized views, and then loop through those and "select 1" from each table returned.

>Solution :

but it seems like BQ will not allow this.

You can try it with a dynamic sql.

FOR record IN
  (SELECT CONCAT('project.dataset.', table_name) as full_name from `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type = 'MATERIALIZED VIEW')
DO
  EXECUTE IMMEDIATE FORMAT("SELECT 1 FROM `%s`;", record.full_name);
END FOR;