CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
mysql_go
mysql_go

Reputation: 2427

SHOW TABLES statement with multiple LIKE values

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 ?

Upvotes: 41

Views: 110007

Answers (6)

Promo IL
Promo IL

Reputation: 160

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

Upvotes: 0

Phoenix
Phoenix

Reputation: 1528

You take table list using the below code

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

Upvotes: 3

uttam chaudhary white elephant
uttam chaudhary white elephant

Reputation: 21

this will help

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

Upvotes: 1

Rich Adams
Rich Adams

Reputation: 26574

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%';

Upvotes: 75

Edwin Dalorzo
Edwin Dalorzo

Reputation: 78589

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

Upvotes: 7

Michael Low
Michael Low

Reputation: 24506

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

SHOW TABLES WHERE Tables_in_tianyan LIKE '%cms%'

Upvotes: 18

Related Questions