mysql - Select table from database where value is X -

so far i've found out gives me list of tables has column name "store_id" - want select columns if "store_id" = 4, how this?

right use find tables has "store_id" column.

select distinct table_name  information_schema.columns column_name in ('store_id') , table_schema='db1'; 

you can dynamic sql this

set @sql = null;  select group_concat(distinct           concat('select ''', table_name,                  ''' table_name ', table_name,                  ' store_id = 4')          separator ' union ')   @sql   information_schema.columns  column_name = 'store_id'    , table_schema = schema();  set @sql = concat(@sql, ' order table_name');  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

sample output:

 | table_name | -------------| |     table1 | 

here sqlfiddle demo

---

now can simplify things on calling end wrapping stored procedure

delimiter $$ create procedure list_tables(in _column_name varchar(64), in _column_value varchar(256)) begin   set @sql = null;    select group_concat(distinct             concat('select ''', table_name, ''' table_name                      ', table_name,                    ' ', _column_name,  ' = ''', _column_value, '''')            separator ' union ')     @sql     information_schema.columns    column_name = _column_name      , table_schema = schema();    set @sql = concat(@sql, ' order table_name');    prepare stmt @sql;   execute stmt;   deallocate prepare stmt; end$$ delimiter ; 

and use this

call list_tables('store_id', '4'); 

here sqlfiddle demo