sql - schemas - show tables in oracle 11g




Get list of all tables in Oracle? (13)

How do I query an Oracle database to display the names of all tables in it?


For better viewing with sqlplus

If you're using sqlplus you may want to first set up a few parameters for nicer viewing if your columns are getting mangled (these variables should not persist after you exit your sqlplus session ):

set colsep '|'
set linesize 167
set pagesize 30
set pagesize 1000

Show All Tables

You can then use something like this to see all table names:

SELECT table_name, owner, tablespace_name FROM all_tables;

Show Tables You Own

As @Justin Cave mentions, you can use this to show only tables that you own:

SELECT table_name FROM user_tables;

Don't Forget about Views

Keep in mind that some "tables" may actually be "views" so you can also try running something like:

SELECT view_name FROM all_views;

The Results

This should yield something that looks fairly acceptable like:


Below is a commented snippet of SQL queries describing how options you can make use of:

-- need to have select catalog role
SELECT * FROM dba_tables;

-- to see tables of your schema
SELECT * FROM user_tables;

-- tables inside your schema and tables of other schema which you possess select grants on
SELECT * FROM all_tables;


I was looking to get a list of all columns names belonging to a table of a schema sorted by the order of column id.

Here's the query I am using: -

SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'schema_owner_username' AND TABLE_NAME='table_name'
ORDER BY COLUMN_ID ASC;


Querying user_tables and dba_tables didn't work.
This one did:

select table_name from all_tables  

The following query only list the required data, whereas the other answers gave me the extra data which only confused me.

select table_name from user_tables;

There exists 3 datadictinary for this

DBA_TABLES describes all relational tables in the database.

SELECT owner, table_name
  FROM dba_tables

Description of relational tables accessible to the user

SELECT owner, table_name
  FROM all_tables

USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.

SELECT table_name
  FROM user_tables

Try the below data dictionary views.

tabs
dba_tables
all_tables
user_tables

We can get all tables including column details from below query:

SELECT * FROM user_tab_columns;

You can use Oracle Data Dictionary to get information about oracle objects.

You can get list of tables in different ways:

select * 
from dba_tables

or for example:

select * 
from dba_objects 
where object_type = 'TABLE' 

Then you can get table columns using table name:

select * 
from dba_tab_columns

Then you can get list of dependencies (triggers, views and etc.):

select * 
from dba_dependencies
where referenced_type='TABLE' and referenced_name=:t_name 

Then you can get text source of this objects:

select * from dba_source

And you can use USER or ALL views instead of DBA if you want.


    select object_name from user_objects where object_type='TABLE';

----------------OR------------------

    select * from tab;

----------------OR------------------

    select table_name from user_tables;

select * from dba_tables

gives all the tables of all the users only if the user with which you logged in is having the sysdba privileges.





oracle