CODEWITHSUNDEEP
sqloracle-database
benstpierre
benstpierre

Reputation: 33591

How do I list all tables in a schema in Oracle SQL?

How do i list all tables in a schema in Oracle SQL?

Upvotes: 193

Views: 821655

Answers (15)

Jordy
Jordy

Reputation: 1967

Show all tables under all schema each:

SELECT table_name, owner FROM all_tables;

Show all tables with specific schema:

SELECT table_name, owner FROM all_tables WHERE owner = 'SCHEMA_NAME';

Upvotes: 0

Kermit the Frog
Kermit the Frog

Reputation: 178

You can directly run the second query if you know the owner name.

--First you can select what all OWNERS there exist:

SELECT DISTINCT(owner) from SYS.ALL_TABLES;

--Then you can see the tables under by that owner:

SELECT table_name, owner from all_tables where owner like ('%XYZ%');

Upvotes: 6

SergioLeone
SergioLeone

Reputation: 745

Name of the table and rows counter for all tables under OWNER schema:

SELECT table_name, num_rows counter from DBA_TABLES WHERE owner = 'OWNER'

Upvotes: 1

Sreeju
Sreeju

Reputation: 39

SELECT table_name, owner FROM all_tables where owner='schema_name' order by table_name

Upvotes: 1

Vijay Kumar
Vijay Kumar

Reputation: 1

If you need to get the size of the table as well, this will be handy:

select SEGMENT_NAME, PARTITION_NAME, BYTES from user_segments where SEGMENT_TYPE='TABLE' order by 1

Upvotes: 0

yash
yash

Reputation: 29

select * from user_tables;

(showing all tables)

Upvotes: 2

A A Nayak
A A Nayak

Reputation: 43

select TABLE_NAME from user_tables;

Above query will give you the names of all tables present in that user;

Upvotes: 2

Adam Musch
Adam Musch

Reputation: 13638

To see all tables in another schema, you need to have one or more of the following system privileges:

SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE

or the big-hammer, the DBA role.

With any of those, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):

SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

Upvotes: 262

Arsalan Sheikh
Arsalan Sheikh

Reputation: 627

select * from cat;

it will show all tables in your schema cat synonym of user_catalog

Upvotes: 3

chan
chan

Reputation: 65

If you logged in as Normal User without DBA permission you may uses the following command to see your own schema's all tables and views.

select * from tab;

Upvotes: 4

Tom
Tom

Reputation: 45174

SELECT table_name  from all_tables where owner = 'YOURSCHEMA';

Upvotes: 87

SQLMenace
SQLMenace

Reputation: 135181

Try this, replace ? with your schema name

select TABLE_NAME from  INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA =?
  AND TABLE_TYPE = 'BASE TABLE'

Upvotes: 3

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

If you are accessing Oracle with JDBC (Java) you can use DatabaseMetadata class. If you are accessing Oracle with ADO.NET you can use a similar approach.

If you are accessing Oracle with ODBC, you can use SQLTables function.

Otherwise, if you just need the information in SQLPlus or similar Oracle client, one of the queries already mentioned will do. For instance:

select TABLE_NAME from user_tables

Upvotes: 2

Michał Niklas
Michał Niklas

Reputation: 54342

Look at my simple utility to show some info about db schema. It is based on: Reverse Engineering a Data Model Using the Oracle Data Dictionary

Upvotes: 0

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

You can query USER_TABLES

select TABLE_NAME from user_tables

Upvotes: 16

Related Questions