matching tables name with show tables

Question

I'm trying to get all tables name from a database.

In my DB I have tables like:

_table1,
_table2,
table3,
table4,
table5_xrefs

but I want to get only the tables without _ at the beginning and without _xref at the end. So in this example I need only table3 and table4.

I'm using show tables to show all tables name and after I'm using PHP to match the correct table name. I was wondering if I could do the same using only a MySQL query.

Answer 1

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'

Answer 2

You can use LIKE or WHERE in SHOW TABLES queries.