Mysql table name with string concate?

I want to select all those tables which tables name contain another field name

Like

Show Tables Like  '%' + table.table_name_prefex

but this give me an error below.

[Err] 1064 - You 开发者_运维技巧have an error in your SQL syntax; check the

manual that corresponds to your MySQL server version for the right syntax to use near '+table.table_name_prfex' at line 1

Kind of suggests either a very clever or a very dumb schema.

You can't do this with 'SHOW' - as Haim Evgi (POST deleted) said you'll need to use the information schema.

I want to select all those table which is begin from fields in another db

So just do a join....

SELECT t.table_schema
, t.table_name
FROM INFORMATION_SCHEMA.TABLES t
, otherdb.table o
WHERE t.table_name LIKE CONCAT('%',o.table_name_prefex)
AND .... /* filters to select rows from otherdb.table */

If your table really is called table then you need to enclose it in back quotes.

you cant do it like you want

you can do something like

SELECT 
 TABLE_NAME
FROM    
    INFORMATION_SCHEMA.TABLES    
WHERE    
    table_schema = 'mydb' AND    
    table_name LIKE '%mypartname%';

Update according to remark:

SELECT 
     TABLE_NAME
    FROM    
        INFORMATION_SCHEMA.COLUMNS    
    WHERE    
        table_schema = 'mydb' AND
        COLUMN_NAME LIKE '%mypartname%';