Oracle database schema

Question

How to get an Oracle database schema? How to get all table names and columns in those tables and any foreign keys?

Answer 1

How to get all table names

There is a lot you are asking here but this will work for tables from schema:

SELECT *
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = 'SCHEMA_NAME'

This will also work:

SELECT *
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE';

but it will return a lot of sys and system owned table names you do not want to see I think... or do you ? Your question is not so clear...

In the column called OBJECT_NAME are the names of the tables:

SELECT OBJECT_NAME
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE';

and any foreign keys?

I have found this great query here: List of foreign keys and the tables they reference

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
                       AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                          AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = 'YOUR_TABLE_NAME';

I believe you now know what is the deal and how to find other things you need. Do go to the link I have mentioned here and research other answers here also...

Note

If you by any chance are not getting any results from your query's first make sure you have entered your table/schema/.../object names in capital letters like this:

AND a.table_name = 'YOUR_TABLE_NAME';
AND OWNER = 'SCHEMA_NAME'

Or if you do not want to think about it you can TURN THEM ALL TO CAPITAL LETTER STRINGS WITH UPPER:

AND UPPER(OWNER) = UPPER('SCHEMA_NAME')