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

User · Answer

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.....

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Select From all tables - MySQL

Examples related to mysql

Examples related to sql