MySQL loop through tables

Question

I want to output data from every table that contains a first_name column. I put together the following procedure, but in my loop, mysql interprets the table name literally instead of evaluating the variable table_name. What's the fix?

delimiter //

drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done int default false;
    DECLARE table_name CHAR(255);

    DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = "wholesale_production" and COLUMN_NAME LIKE "%first%" ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into table_name;
        if done then
            leave myloop;
        end if;
        select * from `wholesale_production`.table_name where created_at >= '2012-10-01';
    end loop;

    close cur1;
end //

delimiter ;

call hunt();

Regular Jo · Accepted Answer · 2021-02-01 22:39:36Z

16

Try this:

delimiter //

drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done BOOL default false;
    DECLARE tablename CHAR(255);

    DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = "wholesale_production" and COLUMN_NAME LIKE "%first%" ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into tablename;
        if done then
            leave myloop;
        end if;
        set @sql = CONCAT('select * from `wholesale_production`.', tablename, ' where created_at >= ''2012-10-01''');
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
end //

delimiter ;

call hunt();

edited Feb 1, 2021 at 22:39

Regular Jo

5,5383 gold badges28 silver badges54 bronze badges

answered Oct 4, 2012 at 0:26

Ross Smith II

12.2k1 gold badge41 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kal Over a year ago

I have a similar problem. If I don't care about filtering the table name to be LIKE '%first%', can I just use SHOW TABLES instead of SELECT TABLE_NAME ...?

Dominic Over a year ago

table_name is a reserved name. should be changed to a different name.

Mr. Developerdude Over a year ago

SELECT distinct TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = "wholesale_production" ;

Daniel Wondyifraw · Accepted Answer · 2015-04-27 10:00:47Z

a little edit of the above to itertate ahtoug all the tables and select them.

delimiter //
drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done int default false;
    DECLARE table_name CHAR(255);

    DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                  WHERE table_schema ='mbu4u';
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;
       myloop: loop
        fetch cur1 into table_name;
        if done then
            leave myloop;
        end if;
        set @sql = CONCAT('select * from `mbu4u`.',table_name);
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
end //

delimiter //

Pau Dominguez · Accepted Answer · 2017-04-26 10:35:37Z

1

table_name is a reserved token use another variable name in "DECLARE table_name CHAR(255);"

answered Apr 26, 2017 at 10:35

Pau Dominguez

1881 gold badge1 silver badge10 bronze badges

Comments

dev24-7 · Accepted Answer · 2012-10-04 00:16:25Z

-3

in cur1 you are using TABLE_NAME there try using a real name of the table

answered Oct 4, 2012 at 0:16

dev24-7

73 bronze badges

1 Comment

Barmar Over a year ago

But the table isn't known, he's getting table names from INFORMATION_SCHEMA.

Collectives™ on Stack Overflow

MySQL loop through tables

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Linked

Related