mysql search for segment of table name

Question

I'm trying to select tables in a mysql database which have the same ending, e.g. staff_name, manager_name, customer_name (example). I was thinking of something along the lines of the following because it works if I have a definite table name.

SELECT * FROM "%_name";

I also tried this using a % instead of the _ with no luck. I'm not sure if there are other wildcards I could try. I've looked around, but none of the results seem to apply to parts of table names :/ Any suggestions?

Answer 1

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%_name'
and TABLE_SCHEMA = 'your_db_name'

If you want to search for tables in your current DB you can do

SHOW TABLES LIKE '%_name'

Answer 2

you can do in 3 way

show tables like '%yourtablename'
show tables like '%yourtablename%'

show tables like 'yourtablename%'