title: Mysql select all existing tables from current database using stored procedure
body: <p>I am still trying to solve this Mysql issue. I'm not a dba but need to figure out how to do it.</p>
  
  <p>I need to run a select statement over all (50k) existing tables from current db.
  Please note that union is not the correct way for me since I have more than 50k tables, I need to solve this with a loop.</p>
  
  <p>So far, I have been trying with two approaches without success:</p>
  
  <p>First: using a subquery and the <code>information_schemma</code> like:</p>
  
  <pre><code>Select * 
  from 
      (SELECT TABLE_NAME 
       FROM INFORMATION_SCHEMA.TABLES  
       WHERE table_schema = 'my_db')
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Select * from (show tables;);
  </code></pre>
  
  <p>Second: using a stored procedure like:</p>
  
  <pre><code>delimiter //
  CREATE PROCEDURE get_all_tables()
  BEGIN
    DECLARE a varchar(100); 
    DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE table_schema = 'my_db';
    OPEN cur1;
  </code></pre>
  
  <p>But neither is doing what I want.</p>
  
  <p>It yields syntax or conceptual errors.</p>
  
  <p>BTW: I already solve this using an external perl script performing a "show tables" and running  a select withing a loop.</p>
  
  <p>This is ok but I think this should be solved with pure mysql.</p>
  
  <p>Any idea would be welcome.</p>
  
  <p>Leo.</p>