title: Populate an array in BigQuery using results from EXECUTE IMMEDIATE
body: <p>I'm using BigQuery's DynamicSQL to dynamically construct queries so that I can re-use code.</p>
  <p>In my use case, I'm trying to build a re-usable SQL script to <a href="https://stackoverflow.com/questions/43457651/bigquery-select-tables-from-all-tables-within-project">get meta data of all tables in a project</a>.</p>
  <p>Where I'm getting stuck is trying to populate an array with the results of an <code>EXECUTE IMMEDIATE</code> command.</p>
  <p>Here is a simple snippet which illustrates my problem:</p>
  <pre><code>DECLARE project_name string;
  DECLARE schema_query string;
  DECLARE schemas ARRAY&lt;string&gt;;
  
  
  SET project_name = 'my_project';
  
  -- Get all the schemas in a project 
  set schema_query = format(&quot;&quot;&quot;
  select schema_name from `%s.INFORMATION_SCHEMA.SCHEMATA`&quot;&quot;&quot;, 
  project_name
  );
  
  -- Add the schemas to an array
  -- THIS COMMAND FAILS
  set schemas = ARRAY(execute immediate schema_query)
  
  
  </code></pre>
  <p>One way to get the desired result, but which is not dynamic, is:</p>
  <pre><code>SET schemas = ARRAY(select schema_name from `my_project.INFORMATION_SCHEMA.SCHEMATA`);
  </code></pre>
  <p>So I want to be able to pass <code>project_name</code> as a parameter and then run the dynamic sql query, which returns a single column of type string, and save the results in an array of strings.</p>