Writing dynamic query in BQ to to pull list of all tables from numerous data_sets (schema) based on a Column_name search. If the column_name = ‘source’ in any of the Data_sets/tables from one Project space – return a list of all data_sets and tables Names that contain that column name.
The current query (below) returns the correct results. However, since the query is dynamic the ‘execute immediate’ will be hit numerous times based on the numbers Data_sets under the project space,the result screen in BQ will return a “result set” for each Execution. In my case over a 100+
and the majority will be “There is no data to display.” (empty result). So i have to click on each “View Results” row to display the results. Can I apply some logic that can

1. Only return “View Results” if query does return data?
2. Append all the results to a temp table or array that could be displayed in ONE result set?

---bq sql---

DECLARE schema_list ARRAY<STRING>;`
DECLARE iter INT64 DEFAULT 0;
DECLARE query_string, Col_Search STRING;


Set col_Search ='source';

SET schema_list = (
  SELECT
    ARRAY_AGG(schema_name)
  FROM
vz-it-pr-cqhv-cdldo-0.INFORMATION_SCHEMA.SCHEMATA
  WHERE
    schema_name in ('fin_nwk_prd_tbls_v','fin_dlp_prd_tbls_v'));  ---<-- this line will be removed once my issue is resolved so all data_sets are passed in the loop 


 WHILE
  iter < ARRAY_LENGTH(schema_list) DO
    SET query_string = "SELECT table_catalog, table_schema, table_name , Column_name FROM " || "vz-it-pr-cqhv-cdldo-0."
    || schema_list[OFFSET(iter)] || ".INFORMATION_SCHEMA.COLUMNS where Column_name = '" || Col_Search || "'"`;
    EXECUTE IMMEDIATE query_string;
    SET iter = iter + 1;
 END WHILE;

--- BQ RESULT WINDOW----`

example of bq result set

Click on one result set and see empty set

Click on one result set and see data returned

Goal is to NOT click on each result to view the actual data returned or eliminate empty results.

I work in an environment where BQ is pretty locked downs. No API calls or GCP Console or Python. Work must completed in BigQuery.

Thanks for any help!

Goal is to NOT click on each result to view the actual data returned or eliminate empty results.