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

Question

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

User · Answer

Try this  replace   with your schema name  select TABLE NAME from  INFORMATION SCHEMA TABLES  WHERE TABLE SCHEMA      AND TABLE TYPE    BASE TABLE

User · Answer

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

User · Answer

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

User · Answer

SELECT table name  from all tables where owner    YOURSCHEMA

User · Answer

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

User · Answer

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

User · Answer

select   from user tables     showing all tables

User · Answer

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

User · Answer

select TABLE NAME from user tables    Above query will give you the names of all tables present in that user

User · Answer

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

User · Answer

You can query USER TABLES  select TABLE NAME from user tables

User · Answer

select   from cat    it will show all tables in your schema cat synonym of user catalog

User · Answer

SELECT  table name  owner FROM all tables where owner  schema name  order by table name

User · Answer

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

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

Examples related to sql

Examples related to oracle