Select From all tables - MySQL

Question

I have a mySQL database called listDB that contain several tables with column name Product etc. I want to SELECT from all tables where Product Like %XYZ%, and display the search result in a separate table.

I tried this:

SELECT * FROM * WHERE Product LIKE %XYZ%

But it is not working. What is the right query for this purpose?

use `union` .. `UNION is used to combine the result from multiple SELECT statements into a single result set. ` — Suhel Meman, Feb 18 '13 at 07:02
Try this SELECT * FROM listDB .* WHERE Product LIKE '%XYZ%'; — Parvathy, Feb 18 '13 at 07:02
@Parvathy - This query is not working.. Syntax error occurred — Farid-ur-Rahman, Feb 18 '13 at 07:54
You should really rethink your database schema if you are having to do this. — Colonel Thirty Two, Jul 31 '13 at 19:19

score 13 · Answer 1 · edited May 23 '17 at 12:17

You get all tables containing the column product using this statment:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('Product')
        AND TABLE_SCHEMA='YourDatabase';

Then you have to run a cursor on these tables so you select eachtime:

Select * from OneTable where product like '%XYZ%'

The results should be entered into a 3rd table or view, take a look here.

Notice: This can work only if the structure of all table is similar, otherwise aou will have to see which columns are united for all these tables and create your result table / View to contain only these columns.

in sqlplus its: SELECT DISTINCT TABLE_NAME FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME IN ('Product') — Dr.Seltsam, Jun 02 '21 at 13:52

score 8 · Answer 2 · edited Sep 25 '13 at 17:23

SELECT product FROM Your_table_name WHERE Product LIKE '%XYZ%';

The above statement will show result from a single table. If you want to add more tables then simply use the UNION statement.

SELECT product FROM Table_name_1 
WHERE Product LIKE '%XYZ%'  
UNION  
SELECT product FROM Table_name_2 
WHERE Product LIKE '%XYZ%'  
UNION  
SELECT product FROM Table_name_3 
WHERE Product LIKE '%XYZ%'

... and so on

score 3 · Answer 3 · answered Mar 20 '13 at 19:09

why you dont just dump the mysql database but with extension when you run without --single-transaction you will interrupt the connection to other clients:

mysqldump --host=hostname.de --port=0000 --user=username --password=password --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 datenbankname > mysqlDBBackup.sql

after that read out the file and search for what you want.... in Strings.....

score 3 · Answer 4 · answered Oct 20 '20 at 16:26

Improve to @inno answer

delimiter //

DROP PROCEDURE IF EXISTS get_product;  
CREATE PROCEDURE get_product()
BEGIN
   DECLARE i VARCHAR(100); 
   DECLARE cur1 CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE COLUMN_NAME IN ('Product');
   OPEN cur1;

   read_loop: LOOP
       FETCH cur1 INTO i;
    
       SELECT i; -- printing table name
    
       SET @s = CONCAT('select * from ', i, ' where Product like %XYZ%'); 
       PREPARE stmt1 FROM @s;
       EXECUTE stmt1;
       DEALLOCATE PREPARE stmt1;

   END LOOP read_loop;

   CLOSE cur1;
END//

delimiter ;

call get_product();

score 2 · Answer 5 · answered Feb 18 '13 at 07:07

As Suhel Meman said in the comments:

SELECT column1, column2, column3 FROM table 1
UNION
SELECT column1, column2, column3 FROM table 2
...

would work.

But all your SELECTS would have to consist of the same amount of columns. And because you are displaying it in one resulting table they should contain the same information.

What you might want to do, is a JOIN on Product ID or something like that. This way you would get more columns, which makes more sense most of the time.

score 2 · Answer 6 · answered Feb 18 '13 at 07:14

You can get all tables that has column "Product" from information_Schema.columns

SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ="Product";

Nor create a procedure

delimiter //
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE a varchar(100); 
  DECLARE cur1 CURSOR FOR SELECT DISTINCT table_name FROM information_schema.columns WHERE column_name ="Product";
  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO a;

    SELECT * FROM a;

  END LOOP;

  CLOSE cur1;
END;

delimiter ;

call curdemo();

This won't work. `SELECT * FROM a` will try to select from a table literally named `a`, it won't treat it as a variable. — Barmar, Dec 31 '20 at 14:49

Select From all tables - MySQL

6 Answers6

Linked

Related