How to list all tables that have a matching string in its name

Question

In Oracle DB, how to list all tables that exist in a schema with the table names having a substring like Student? Say you have a list of tables like College_student, Student_Offer or Student_Dept etc..

Answer 1

You may query the all_tables table:

SELECT table_name
FROM all_tables
WHERE table_name LIKE '%student%';