How do you run a query to list all tables, or all tables where tableName like '%abc%'.

Just poking around, I tried the following with no success:

select * from SYS.TAB$
select * from SYS.OBJ$ where Name like '%abc%'

I'm trying to re-learn in Oracle some basic things I know in MSSQL.


Depending on your privilege level, you'd want to use DBA_TABLES, ALL_TABLES, or USER_TABLES. There is a StackOverflow thread that walks through this in some detail.

By default, Oracle identifiers are going to be stored in upper case in the data dictionary and queries are case-sensitive. Most likely, if you have a table whose name contains the string "abc", you'd need to search in upper case, i.e.

  FROM dba_tables
 WHERE table_name LIKE '%ABC%'
  • Perfect, and I can filter on OWNER as well. Dec 18 '14 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.