Mysql select all existing tables from current database using stored procedure

Question

I am still trying to solve this Mysql issue. I'm not a dba but need to figure out how to do it.

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.

So far, I have been trying with two approaches without success:

First: using a subquery and the information_schemma like:

Select * 
from 
    (SELECT TABLE_NAME 
     FROM INFORMATION_SCHEMA.TABLES  
     WHERE table_schema = 'my_db')

or

Select * from (show tables;);

Second: using a stored procedure like:

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;

But neither is doing what I want.

It yields syntax or conceptual errors.

BTW: I already solve this using an external perl script performing a "show tables" and running a select withing a loop.

This is ok but I think this should be solved with pure mysql.

Any idea would be welcome.

Leo.

check out my self-answer here http://stackoverflow.com/q/38693720 — Drew, Nov 21 '16 at 21:35

score 0 · Answer 1 · answered Nov 21 '16 at 21:43

SELECT * FROM (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'my_db')

First of all, you can't do this in any implementation of SQL. The table name must be known by the query at prepare-time, not at run-time. You can't select from a string, you have to select from a table identifier.

It's the difference between these two queries:

SELECT * FROM MyTable    -- identifier
SELECT * FROM 'MyTable'  -- string value (this won't work)

By the way, most programming languages have a similar concept, of a function or class name being different from the name of that function or class. You can't execute a function by its name using the same syntax as you would execute it by its identifier.

<?php
$result = myFunction();
$result = 'myFunction'(); // nonsense

But some languages do have ways of getting around this:

<?php
$funcName = 'myFunction';
$result = $funcName();

In SQL, you can get around this limitation by using the table name as you build a new SQL query as a string. Then prepare and execute that SQL query string at runtime. This is called a dynamic SQL query.

But like the PHP example above of using a variable to store the name of the function, using dynamic SQL requires multiple steps. You can't combine the query to get your table names into the same query that uses the results.

You were on the right track with your stored procedure that opens a cursor for the set of table names. But you can't open cursors from dynamic SQL statements in stored procedures.

You can do what you need pretty easily using dynamic SQL in a scripting language like PHP or Python.

Dear Bill , thanks for your response !!! I will read a little bit more about procedures. Regards. — user2977753, Nov 22 '16 at 12:53

Mysql select all existing tables from current database using stored procedure

1 Answers1