Get list of all tables in Oracle

Question

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

User · Answer

To get all the table names  we can use  Select  owner  table name  from all tables   if you have dba permission  you can use  Select owner  table name from dba tables

User · Answer

Try selecting from user tables which lists the tables owned by the current user

User · Answer

SELECT owner  table name   FROM dba tables   This is assuming that you have access to the DBA TABLES data dictionary view   If you do not have those privileges but need them  you can request that the DBA explicitly grants you privileges on that table  or  that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT CATALOG ROLE role  either of which would allow you to query any data dictionary table    Of course  you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don t care about   Alternatively  if you do not have access to DBA TABLES  you can see all the tables that your account has access to through the ALL TABLES view   SELECT owner  table name   FROM all tables   Although  that may be a subset of the tables available in the database  ALL TABLES shows you the information for all the tables that your user has been granted access to      If you are only concerned with the tables that you own  not those that you have access to  you could use USER TABLES   SELECT table name   FROM user tables   Since USER TABLES only has information about the tables that you own  it does not have an OWNER column     the owner  by definition  is you   Oracle also has a number of legacy data dictionary views-- TAB  DICT  TABS  and CAT for example-- that could be used   In general  I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6   Oracle has not changed these views in a long time so they often have problems with newer types of objects   For example  the TAB and CAT views both show information about tables that are in the user s recycle bin while the  DBA ALL USER  TABLES views all filter those out   CAT also shows information about materialized view logs with a TABLE TYPE of  TABLE  which is unlikely to be what you really want   DICT combines tables and synonyms and doesn t tell you who owns the object

User · Answer

Going one step further  there is another view called cols  all tab columns  which can be used to ascertain which tables contain a given column name   For example   SELECT table name  column name FROM cols WHERE table name LIKE  EST   AND column name LIKE   CALLREF      to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names   This can help when working out what columns you want to join on  for example  depending on your table and column naming conventions

User · Answer

Try selecting from user tables which lists the tables owned by the current user

User · Answer

I did not find answer which would point to use   DBA ALL TABLES  ALL ALL TABLES USER ALL TABLES    so decided to add my version as well  This view actually returns more that DBA TABLES as it returns object tables as well  http   docs oracle com cd E11882 01 server 112 e40402 statviews 1003 htm

User · Answer

Oracle database to display the names of all tables using below query   SELECT owner  table name FROM dba tables   SELECT owner  table name FROM all tables   SELECT table name FROM user tables   vist more   http   www plsqlinformation com 2016 08 get-list-of-all-tables-in-oracle html

User · Answer

Tables in the current user - logon schema select   from tabs

User · Answer

With any of those  you can select   SELECT DISTINCT OWNER  OBJECT NAME      FROM DBA OBJECTS      WHERE OBJECT TYPE    TABLE  AND OWNER  SOME SCHEMA NAME    SELECT DISTINCT OWNER  OBJECT NAME      FROM ALL OBJECTS      WHERE OBJECT TYPE    TABLE  AND OWNER  SOME SCHEMA NAME

User · Answer

I did not find answer which would point to use   DBA ALL TABLES  ALL ALL TABLES USER ALL TABLES    so decided to add my version as well  This view actually returns more that DBA TABLES as it returns object tables as well  http   docs oracle com cd E11882 01 server 112 e40402 statviews 1003 htm

User · Answer

Oracle database to display the names of all tables using below query   SELECT owner  table name FROM dba tables   SELECT owner  table name FROM all tables   SELECT table name FROM user tables   vist more   http   www plsqlinformation com 2016 08 get-list-of-all-tables-in-oracle html

User · Answer

Including views   SELECT owner  table name as table view   FROM dba tables UNION ALL SELECT owner  view name as table view   FROM DBA VIEWS

User · Answer

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

User · Answer

Querying user tables and dba tables didn t work  This one did     select table name from all tables

User · Answer

Try the below data dictionary views   tabs dba tables all tables user tables

User · Answer

Execute the below commands  Show all tables in the Oracle Database sql gt  SELECT table name FROM dba tables  Show tables owned by the current user sql gt  SELECT table name FROM user tables  Show tables that are accessible by the current user sql gt  SELECT table name FROM all tables ORDER BY table name

User · Answer

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

User · Answer

With any of those  you can select   SELECT DISTINCT OWNER  OBJECT NAME      FROM DBA OBJECTS      WHERE OBJECT TYPE    TABLE  AND OWNER  SOME SCHEMA NAME    SELECT DISTINCT OWNER  OBJECT NAME      FROM ALL OBJECTS      WHERE OBJECT TYPE    TABLE  AND OWNER  SOME SCHEMA NAME

User · Answer

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

User · Answer

Going one step further  there is another view called cols  all tab columns  which can be used to ascertain which tables contain a given column name   For example   SELECT table name  column name FROM cols WHERE table name LIKE  EST   AND column name LIKE   CALLREF      to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names   This can help when working out what columns you want to join on  for example  depending on your table and column naming conventions

User · Answer

Execute the below commands  Show all tables in the Oracle Database sql gt  SELECT table name FROM dba tables  Show tables owned by the current user sql gt  SELECT table name FROM user tables  Show tables that are accessible by the current user sql gt  SELECT table name FROM all tables ORDER BY table name

User · Answer

Querying user tables and dba tables didn t work  This one did     select table name from all tables

User · Answer

Including views   SELECT owner  table name as table view   FROM dba tables UNION ALL SELECT owner  view name as table view   FROM DBA VIEWS

User · Answer

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

User · Answer

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

User · Answer

Try selecting from user tables which lists the tables owned by the current user

User · Answer

A new feature available in SQLcl  which is a free command line interface for Oracle Database   is  Tables alias   Here are few examples showing the usage and additional aspects of the feature  First  connect to a sql command line  sql exe in windows  session  It is recommended to enter this sqlcl specific command before running any other commands or queries which display data   SQL gt  set sqlformat ansiconsole     -- resizes the columns to the width of the                                     -- data to save space    SQL gt  tables  TABLES ----------- REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB HISTORY      To know what the tables alias is referring to  you may simply use alias list  lt alias gt   SQL gt  alias list tables tables - tables  lt schema gt  - show tables from schema --------------------------------------------------   select table name  TABLES  from user tables   You don t have to define this alias as it comes by default under SQLcl  If you want to list tables from a specific schema  using a new user-defined alias and passing schema name as a bind argument  with only a set of columns being displayed  you may do so using  SQL gt  alias tables schema   select owner  table name  last analyzed from all tables where owner    ownr   Thereafter you may simply pass schema name as an argument  SQL gt  tables schema HR  OWNER   TABLE NAME               LAST ANALYZED HR      DUMMY1                   18-10-18 HR      YOURTAB2                 16-11-18 HR      YOURTABLE                01-12-18 HR      ID TABLE                 05-12-18 HR      REGIONS                  26-05-18 HR      LOCATIONS                26-05-18 HR      DEPARTMENTS              26-05-18 HR      JOBS                     26-05-18 HR      EMPLOYEES                12-10-18         A more sophisticated pre-defined alias is known as Tables2  which displays several other columns   SQL gt  tables2  Tables        TABLE NAME                 NUM ROWS   BLOCKS   UNFORMATTED SIZE COMPRESSION     INDEX COUNT   CONSTRAINT COUNT   PART COUNT LAST ANALYZED AN IP TABLE                       0        0                  0 Disabled                  0                  0            0  gt  Month PARTTABLE                         0        0                  0                           1                  0            1  gt  Month TST2                              0        0                  0 Disabled                  0                  0            0  gt  Month TST3                              0        0                  0 Disabled                  0                  0            0  gt  Month MANAGE EMPLYEE                    0        0                  0 Disabled                  0                  0            0  gt  Month PRODUCT                           0        0                  0 Disabled                  0                  0            0  gt  Month ALL TAB X78EHRYFK                 0        0                  0 Disabled                  0                  0            0  gt  Month TBW                               0        0                  0 Disabled                  0                  0            0  gt  Month DEPT                              0        0                  0 Disabled                  0                  0            0  gt  Month   To know what query it runs in the background  enter  alias list tables2   This will show you a slightly more complex query along with predefined column definitions commonly used in SQL Plus   Jeff Smith explains more about aliases here

User · Answer

A new feature available in SQLcl  which is a free command line interface for Oracle Database   is  Tables alias   Here are few examples showing the usage and additional aspects of the feature  First  connect to a sql command line  sql exe in windows  session  It is recommended to enter this sqlcl specific command before running any other commands or queries which display data   SQL gt  set sqlformat ansiconsole     -- resizes the columns to the width of the                                     -- data to save space    SQL gt  tables  TABLES ----------- REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB HISTORY      To know what the tables alias is referring to  you may simply use alias list  lt alias gt   SQL gt  alias list tables tables - tables  lt schema gt  - show tables from schema --------------------------------------------------   select table name  TABLES  from user tables   You don t have to define this alias as it comes by default under SQLcl  If you want to list tables from a specific schema  using a new user-defined alias and passing schema name as a bind argument  with only a set of columns being displayed  you may do so using  SQL gt  alias tables schema   select owner  table name  last analyzed from all tables where owner    ownr   Thereafter you may simply pass schema name as an argument  SQL gt  tables schema HR  OWNER   TABLE NAME               LAST ANALYZED HR      DUMMY1                   18-10-18 HR      YOURTAB2                 16-11-18 HR      YOURTABLE                01-12-18 HR      ID TABLE                 05-12-18 HR      REGIONS                  26-05-18 HR      LOCATIONS                26-05-18 HR      DEPARTMENTS              26-05-18 HR      JOBS                     26-05-18 HR      EMPLOYEES                12-10-18         A more sophisticated pre-defined alias is known as Tables2  which displays several other columns   SQL gt  tables2  Tables        TABLE NAME                 NUM ROWS   BLOCKS   UNFORMATTED SIZE COMPRESSION     INDEX COUNT   CONSTRAINT COUNT   PART COUNT LAST ANALYZED AN IP TABLE                       0        0                  0 Disabled                  0                  0            0  gt  Month PARTTABLE                         0        0                  0                           1                  0            1  gt  Month TST2                              0        0                  0 Disabled                  0                  0            0  gt  Month TST3                              0        0                  0 Disabled                  0                  0            0  gt  Month MANAGE EMPLYEE                    0        0                  0 Disabled                  0                  0            0  gt  Month PRODUCT                           0        0                  0 Disabled                  0                  0            0  gt  Month ALL TAB X78EHRYFK                 0        0                  0 Disabled                  0                  0            0  gt  Month TBW                               0        0                  0 Disabled                  0                  0            0  gt  Month DEPT                              0        0                  0 Disabled                  0                  0            0  gt  Month   To know what query it runs in the background  enter  alias list tables2   This will show you a slightly more complex query along with predefined column definitions commonly used in SQL Plus   Jeff Smith explains more about aliases here

User · Answer

Try selecting from user tables which lists the tables owned by the current user

User · Answer

Querying user tables and dba tables didn t work  This one did     select table name from all tables

User · Answer

To get all the table names  we can use  Select  owner  table name  from all tables   if you have dba permission  you can use  Select owner  table name from dba tables

User · Answer

We can get all tables including column details from below query   SELECT   FROM user tab columns

User · Answer

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

User · Answer

Simple query to select the tables for the current user     SELECT table name FROM user tables

User · Answer

Simple query to select the tables for the current user     SELECT table name FROM user tables

User · Answer

Indeed  it is possible to have the list of tables via SQL queries it is possible to do that also via tools that allow the generation of data dictionaries  such as ERWIN  Toad Data Modeler or ERBuilder  With these tools  in addition to table names  you will have fields  their types  objects like triggers  sequences  domain  views      Below steps to follow to generate your tables definition    You have to reverse engineer your database    In Toad data modeler   Menu -  File -  reverse engineer -  reverse engineering wizard In ERBuilder data modeler  Menu -  File -  reverse engineer    Your database will be displayed in the software as an Entity Relationship diagram    Generate your data dictionary that will contain your Tables definition   In Toad data modeler  Menu -  Model -  Generate report -  Run In ERBuilder data modeler  Menu -  Tool -  generate model documentation

User · Answer

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

User · Answer

Try the below data dictionary views   tabs dba tables all tables user tables

User · Answer

SELECT owner  table name   FROM dba tables   This is assuming that you have access to the DBA TABLES data dictionary view   If you do not have those privileges but need them  you can request that the DBA explicitly grants you privileges on that table  or  that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT CATALOG ROLE role  either of which would allow you to query any data dictionary table    Of course  you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don t care about   Alternatively  if you do not have access to DBA TABLES  you can see all the tables that your account has access to through the ALL TABLES view   SELECT owner  table name   FROM all tables   Although  that may be a subset of the tables available in the database  ALL TABLES shows you the information for all the tables that your user has been granted access to      If you are only concerned with the tables that you own  not those that you have access to  you could use USER TABLES   SELECT table name   FROM user tables   Since USER TABLES only has information about the tables that you own  it does not have an OWNER column     the owner  by definition  is you   Oracle also has a number of legacy data dictionary views-- TAB  DICT  TABS  and CAT for example-- that could be used   In general  I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6   Oracle has not changed these views in a long time so they often have problems with newer types of objects   For example  the TAB and CAT views both show information about tables that are in the user s recycle bin while the  DBA ALL USER  TABLES views all filter those out   CAT also shows information about materialized view logs with a TABLE TYPE of  TABLE  which is unlikely to be what you really want   DICT combines tables and synonyms and doesn t tell you who owns the object

User · Answer

Querying user tables and dba tables didn t work  This one did     select table name from all tables

User · Answer

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

User · Answer

Indeed  it is possible to have the list of tables via SQL queries it is possible to do that also via tools that allow the generation of data dictionaries  such as ERWIN  Toad Data Modeler or ERBuilder  With these tools  in addition to table names  you will have fields  their types  objects like triggers  sequences  domain  views      Below steps to follow to generate your tables definition    You have to reverse engineer your database    In Toad data modeler   Menu -  File -  reverse engineer -  reverse engineering wizard In ERBuilder data modeler  Menu -  File -  reverse engineer    Your database will be displayed in the software as an Entity Relationship diagram    Generate your data dictionary that will contain your Tables definition   In Toad data modeler  Menu -  Model -  Generate report -  Run In ERBuilder data modeler  Menu -  Tool -  generate model documentation

User · Answer

SELECT owner  table name   FROM dba tables   This is assuming that you have access to the DBA TABLES data dictionary view   If you do not have those privileges but need them  you can request that the DBA explicitly grants you privileges on that table  or  that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT CATALOG ROLE role  either of which would allow you to query any data dictionary table    Of course  you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don t care about   Alternatively  if you do not have access to DBA TABLES  you can see all the tables that your account has access to through the ALL TABLES view   SELECT owner  table name   FROM all tables   Although  that may be a subset of the tables available in the database  ALL TABLES shows you the information for all the tables that your user has been granted access to      If you are only concerned with the tables that you own  not those that you have access to  you could use USER TABLES   SELECT table name   FROM user tables   Since USER TABLES only has information about the tables that you own  it does not have an OWNER column     the owner  by definition  is you   Oracle also has a number of legacy data dictionary views-- TAB  DICT  TABS  and CAT for example-- that could be used   In general  I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6   Oracle has not changed these views in a long time so they often have problems with newer types of objects   For example  the TAB and CAT views both show information about tables that are in the user s recycle bin while the  DBA ALL USER  TABLES views all filter those out   CAT also shows information about materialized view logs with a TABLE TYPE of  TABLE  which is unlikely to be what you really want   DICT combines tables and synonyms and doesn t tell you who owns the object

User · Answer

SELECT owner  table name   FROM dba tables   This is assuming that you have access to the DBA TABLES data dictionary view   If you do not have those privileges but need them  you can request that the DBA explicitly grants you privileges on that table  or  that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT CATALOG ROLE role  either of which would allow you to query any data dictionary table    Of course  you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don t care about   Alternatively  if you do not have access to DBA TABLES  you can see all the tables that your account has access to through the ALL TABLES view   SELECT owner  table name   FROM all tables   Although  that may be a subset of the tables available in the database  ALL TABLES shows you the information for all the tables that your user has been granted access to      If you are only concerned with the tables that you own  not those that you have access to  you could use USER TABLES   SELECT table name   FROM user tables   Since USER TABLES only has information about the tables that you own  it does not have an OWNER column     the owner  by definition  is you   Oracle also has a number of legacy data dictionary views-- TAB  DICT  TABS  and CAT for example-- that could be used   In general  I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6   Oracle has not changed these views in a long time so they often have problems with newer types of objects   For example  the TAB and CAT views both show information about tables that are in the user s recycle bin while the  DBA ALL USER  TABLES views all filter those out   CAT also shows information about materialized view logs with a TABLE TYPE of  TABLE  which is unlikely to be what you really want   DICT combines tables and synonyms and doesn t tell you who owns the object

User · Answer

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

User · Answer

select object name from user objects where object type  TABLE     ----------------OR------------------      select   from tab    ----------------OR------------------      select table name from user tables

User · Answer

select   from all all tables   this additional  all  at the beginning gives extra 3 columns which are   OBJECT ID TYPE TABLE TYPE OWNER TABLE TYPE

User · Answer

select   from all all tables   this additional  all  at the beginning gives extra 3 columns which are   OBJECT ID TYPE TABLE TYPE OWNER TABLE TYPE

User · Answer

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

User · Answer

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

User · Answer

Tables in the current user - logon schema select   from tabs

User · Answer

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

User · Answer

select object name from user objects where object type  TABLE     ----------------OR------------------      select   from tab    ----------------OR------------------      select table name from user tables

User · Answer

We can get all tables including column details from below query   SELECT   FROM user tab columns

[sql] Get list of all tables in Oracle?

Examples related to sql

Examples related to oracle