Select * from multiple tablenames

Question

I want to select * from every table in my database. How can i do this?

I tried following script:

SELECT * 
  FROM 
     ( SELECT TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES 
         WHERE TABLE_CATALOG = 'databasename'
     )

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'databasename' is already returning every tablename of the database, but how do i select everything out of this 'list'?

Can not understand your question,if want to select multiple table name,can you tell us the query condition? such as in a specify database or with some special name prefix — flyingfox, Jul 19 '18 at 10:16
If you want to select every column,why not use `*` instead of `TABLE_NAME` in the sub query — flyingfox, Jul 19 '18 at 10:20
@David: I don't. It sounds like your data is not normalized or you are trying to reinvent relational databases. So these tables have the same structure? What do you intend doing with the data after retrieving it? — symcbean, Jul 19 '18 at 10:21
@lucumt `INFORMATION_SCHEMA.TABLES` returns all tablenames of the databse, not the content of these tables. — David Walser, Jul 19 '18 at 11:23
https://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables — kry, Jul 19 '18 at 11:24

score 2 · Answer 1 · answered Jul 19 '18 at 11:35

One of those rare occasions where a cursor is useful since you need to iterate information_schema tables, build a prepared statement and execute it (note you can only submit 1 statement at a time to dynamic sql.

drop procedure if exists p;

delimiter $$

CREATE  PROCEDURE p()

begin 
     declare vtable_name varchar(100);
     declare done int;
    declare c7 cursor for SELECT   TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES 
         WHERE TABLE_schema = 'sandbox' and table_type = 'base table' and table_name in ('t', 't1');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open c7;
    cursorloop:loop
        fetch c7 into vtable_name;
        if done = true then  
        leave cursorloop;
        end if;  
        set @sql = concat('select * from ', vtable_name,';');
        select @sql;
        prepare sqlstmt from @sql;
        execute sqlstmt;
        deallocate prepare sqlstmt; 
     end loop cursorloop;
    close c7;
 end $$

 delimiter ;

 call p();

Note I have restricted the cursor select for illustration purposes and have restricted what is returned to base tables (you may or may not want to include views)

MariaDB [sandbox]> call p();
+------------------+
| @sql             |
+------------------+
| select * from t; |
+------------------+
1 row in set (0.00 sec)

+------+------+-------+
| id   | user | value |
+------+------+-------+
|    1 | A    | Cool  |
|    2 | A    | Cool  |
|    3 | A    | Cool  |
|    2 | A    | Warm  |
|    3 | A    | Warm  |
|    4 | B    | Cool  |
|    5 | C    | Cool  |
|    5 | C    | Warm  |
+------+------+-------+
8 rows in set (0.00 sec)

+-------------------+
| @sql              |
+-------------------+
| select * from t1; |
+-------------------+
1 row in set (0.04 sec)

+------+-------+
| sku  | stock |
+------+-------+
|    1 |     5 |
|    2 |     5 |
+------+-------+
2 rows in set (0.04 sec)

Note particularly the built sql statements.

Select * from multiple tablenames

1 Answers1