MySql show tables query

Question

I'm trying to select the tables names in my database using this query

show tables LIKE 'table1%'

it's working fine but at the same table I have a version of the table with some columns empty called 'table1_blank'. When I use the above statement I get every table starting with table1 including the "blank", how do I exclude 'table1_blank' from the selection?

Thanks.

Community · Accepted Answer · 2017-05-23 10:31:44Z

1

You can use multiple like clauses using the where condition for SHOW TABLES:

SHOW TABLES 
FROM `dbname` 
WHERE `Tables_In_dbname` LIKE 'table1%' 
  AND `Tables_In_dbname` NOT LIKE 'table1\_%';

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Nov 25, 2013 at 1:20

matth

6,3344 gold badges40 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

crimson Over a year ago

Thanks. It still returned table1 and table1_blank but I used it with Junsu Heo's other way of selecting tables using information_schema and it worked.

matth Over a year ago

Hmm, I would have had to seen the query that was ran. I created two tables called table1 and table1_blank to test it.

Jason Heo · Accepted Answer · 2013-11-25 01:30:38Z

0

'_' has special meaning. 'any one character', so you need to change like this..

EDITED

SELECT TABLE_NAME
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your database name'
   AND TABLE_NAME LIKE 'table1%'
   AND TABLE_NAME NOT LIKE 'table1\_%';

edited Nov 25, 2013 at 1:30

answered Nov 25, 2013 at 1:12

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

2 Comments

crimson Over a year ago

show tables LIKE 'table1_%' this is giving me table1_blank only.

Jason Heo Over a year ago

@crimson oops. I misunderstood you question.

Collectives™ on Stack Overflow

MySql show tables query

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related