matching tables name with show tables

It's possible but you have to know that column name returned from SHOW TABLES query is concatenation of string tables_in_ and your database name. So it would look like this, for database test:

SHOW TABLES 
      WHERE tables_in_test NOT LIKE '\_%' 
        AND tables_in_test NOT LIKE '%\_xrefs'

But I would prefer to use information_schema database to get this info:

SELECT TABLE_NAME 
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = SCHEMA() /* = 'test'*/
   AND TABLE_NAME NOT LIKE '\_%'
   AND TABLE_NAME NOT LIKE '%\_xrefs'

You can use LIKE or WHERE in SHOW TABLES queries.

Tags:

Mysql