title: List wildcard tables bigquery schema
body: <p>I want to <strong>list the tables</strong> which can be used as <strong>wildcards</strong> in BigQuery.</p>
  <p>My <strong>dataset</strong> has the table list is similar to the following:</p>
  <pre><code>events_122022
  events_122021
  events_122020
  ...
  ...
  events_112012
  ...
  ...
  
  analytics_122022
  analytics_122021
  analytics_122020
  ...
  ...
  analytics_112012
  </code></pre>
  <p>These tables are created dynamically and I have no information on the used table prefix
  Is there a way to find the list of tables which can be used dynamically?</p>
  <p>The result should be:<strong><code>[events_, analytics_]</code></strong></p>
  <h3>My attempt:</h3>
  <p>Find the <strong>tables with similar DDL</strong> using the following SQL</p>
  <pre><code>   SELECT
      SUBSTR(ddl, STRPOS(ddl, '(')) as commonDDL,
      STRING_AGG(table_name) as table
    FROM
      dataset.INFORMATION_SCHEMA.TABLES
    GROUP BY SUBSTR(ddl, STRPOS(ddl, '('))
  </code></pre>
  <p>This gives the <strong>output</strong> as :</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>commonDDL</th>
  <th>table</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>(ID STRING, ...)</td>
  <td>events_122022, events_122021, ...</td>
  </tr>
  <tr>
  <td>(NAME STRING, ...)</td>
  <td>analytics_112022, analytics_112021 ...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Now using a Longest common shared start algorithm I can find the required result.
  (Longest common start code <a href="https://stackoverflow.com/questions/1916218/find-the-longest-common-starting-substring-in-a-set-of-strings/1917041#1917041">here</a> )</p>
  <p>What are the <strong>other ways we can approach this problem</strong>?</p>
  <p>Couldn't find anything on BigQuery docs.</p>
  <p><em>Note: I only have readonly permission for the BigQuery dataset</em></p>