1

I have 50 databases in mysql, there is an one specific column called product_number in all of them(databases and tables),but the name of the tables are different, is there anyway to select or show them all? as you know better than me in each database and table I can easily show them with this command :

SELECT product_number
FROM 'products'
LIMIT 50

but what about the others ? for example :

use * 
SELECT product_number
FROM *

how is it possible ?

Freeman
  • 9,464
  • 7
  • 35
  • 58
  • 1
    Why do you want to do that? Why you have 50 db? – inetphantom Aug 14 '15 at 07:36
  • @inetphantom, I have 50 portal that generates our products with a specific number and everyday I have to check some of them , I'm just wondering is there anyway to do it quickly ? – Freeman Aug 14 '15 at 07:39
  • 1
    Maybe you can use the INFORMATION_SCHEMA COLUMNS table in the databases and find the tables with the product_number column – AgeDeO Aug 14 '15 at 07:41
  • I can not think of another way than semi-hardcoding, i just do not see why your data architect made 50 tables. – inetphantom Aug 14 '15 at 07:41
  • as @AgeDeO said: select from INFORMATION_SCHEMA COLUMNS, save them into an array/internal table, iterate through it and do your buissness logic in there. – inetphantom Aug 14 '15 at 07:43

1 Answers1

2

You can find all the tables containing the product_number column by accessing the information_schema tables:

SELECT c.`TABLE_SCHEMA`, 
  c.`TABLE_NAME`, 
  c.`COLUMN_NAME` 
FROM `information_schema`.`COLUMNS` c
  WHERE c.`COLUMN_NAME` = 'product_name'
A Hocevar
  • 726
  • 3
  • 17
  • Thnx but it just show me the structure I want the data of 'product_name' ! – Freeman Aug 14 '15 at 07:51
  • You pass the data returned to a `PREPARE` statement and execute it – A Hocevar Aug 14 '15 at 07:54
  • Hm, actually it is not possible, the way MySQL handles the prepared statements prevents you from binding a table name to a variable. You would have to retrieve the list of schema/tables as provided by my query, and then use something different than SQL to loop over it. – A Hocevar Aug 14 '15 at 07:59
  • Yes it is possible. You just need to fiddle around with string concatenation a little. – Mchl Aug 14 '15 at 08:04
  • 1
    See this answer of mine for example of how can this be done: https://stackoverflow.com/questions/3430865/how-to-preload-tables-into-innodb-buffer-pool-with-mysql – Mchl Aug 14 '15 at 08:12
  • @A Hocevar, Thnx for your help ! 1++ – Freeman Aug 14 '15 at 18:09