CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperplsqldeveloper
Spear A1
Spear A1

Reputation: 585

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

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..

Upvotes: 2

Views: 7135

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22427

SELECT table_name
  FROM all_tables
 WHERE owner = :owner
   AND upper(table_name) LIKE '%STUDENT%';

we upper the name first in the predicate because some people insist on case sensitive object names in Oracle.

I run this with STU vs STUDENT in the LIKE search and see these results -

enter image description here

And since you tagged SQL Developer - you can simply browse a schema using the connection tree and optionally add a filter on the name.

enter image description here

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521028

You may query the all_tables table:

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

Upvotes: 4

Related Questions