Get list of table names in oracle from information schema

Question

I am using the following query in MySQL and POSTGRESQL.

MySQL SELECT table_name FROM information_schema.tables where table_schema='SmartHome' and table_name like '%House%'

POSTGRESQL. SELECT table_name FROM information_schema.tables where table_schema='public' and table_name like '%House%'.

I want to know equivalent query in Oracle DB.

score 1 · Answer 1 · answered Sep 16 '17 at 05:38

1

You can use the below sql queries for the same -

select * from user_tables; /* The relational tables owned by the current user.*/

select * from dba_tables;  /* describes all relational tables in the database - For this you need to login as a DBA */

select * from tab; /* Gives you all tables */

select * from cat; /* Gives you sequences as well */

answered Sep 16 '17 at 05:38

Prashanth kumar

949
3
10
32

How can i get list of tables associated with particular database, is this query right? SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='DBName' and TABLE_NAME like '%HOUSE%' – Aravindhan K Sep 16 '17 at 05:46
It should work just fine, just add a litle tweak if you don't want to miss any lower case tables - SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER='DBName' and upper(TABLE_NAME) like '%HOUSE%' – Prashanth kumar Sep 16 '17 at 05:50
If the same solves your concern, please Accept the answer, thanks. – Prashanth kumar Sep 16 '17 at 05:55
Thanks and sure buddy – Aravindhan K Sep 16 '17 at 05:56

score 0 · Answer 2 · answered Sep 16 '17 at 05:21

0

select TABLE_NAME from user_tables

answered Sep 16 '17 at 05:21

The beginner

624
4
17

Get list of table names in oracle from information schema

2 Answers2