SHOW TABLES statement with multiple LIKE values

Question

mysql> SHOW TABLES like 'cms';
+-------------------------+
| Tables_in_tianyan (cms) |
+-------------------------+
| cms                     |
+-------------------------+
1 row in set (0.00 sec)

Result

mysql> SHOW TABLES like 'cms' or like 'role';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual...

How can I filter by multiple conditions ?

Enclave

dev.mysql.com/doc/refman/5.0/en/tables-table.html – Alcaeus

Answer 1

You need to use the WHERE clause. As shown in the docs, you can only have a single pattern if you use "SHOW TABLES LIKE ...", but you can use an expression in the WHERE clause if you use "SHOW TABLES WHERE ...". Since you want an expression, you need to use the WHERE clause.

SHOW TABLES
FROM `<yourdbname>`
WHERE 
    `Tables_in_<yourdbname>` LIKE '%cms%'
    OR `Tables_in_<yourdbname>` LIKE '%role%';

Loreanloredana

You need to define the db name in the FROM clause - this query will return tables from any database served by the MySQL instance. – Alcaeus

Answer 2

You can just use a normal SQL WHERE statement to do it.

SHOW TABLES WHERE Tables_in_tianyan LIKE '%cms%'

Composure

I want to filter by like 'cms' or like 'role', and it seems WHERE Tables_in_tianyan is unnecessary here. – Enclave

Answer 3

show tables from mydb 
where 
  Tables_in_mydb like '%statistics%' 
  or Tables_in_mydb like '%device%';

Chadburn

Answer 4

You take table list using the below code

select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'database_name' 

Teresiateresina

Answer 5

this will help

SELECT 
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
 LIKE 'cms%';

Histamine

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Karnak

Answer 6

SHOW TABLES WHERE Tables_in_<yourdbname> REGEXP '^(regex-pattern1|regex-pattern2|...)$';

Montez