Mysql select all existing tables from current database using stored procedure
<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>