Display all the tables in an Oracle schema, with fk relations and number of records, for the parent and the child tables

 select   a.owner powner, a.table_name tableswithoutpk,
          b.table_name ptable, 
          to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT 
          count(*) c FROM ' || a.owner || '.' || 
           '"'||a.table_name||'"')),'/ROWSET/ROW/C')) pcount,
          b.constraint_name pkname, b.status pkstatus, 
          c.table_name chtable, c.owner chowner, c.constraint_name 
          fkname, c.status fkstatus,
          decode(c.table_name, null, null, 
          to_number(extractvalue(xmltype(dbms_xmlgen.getxml('SELECT 
          count(*) c FROM ' || c.owner || '.' ||                
          '"'||c.table_name||'"')),'/ROWSET/ROW/C'))) chCount 
                 from all_tables a   
                 left outer join all_constraints b on 
                   b.owner='SCOTT' and b.constraint_type='P' and 
                   a.table_name=b.table_name
                 left outer join all_constraints c on 
                   c.R_constraint_name=b.constraint_name  
                 where a.owner='SCOTT' ;


powner and tableswithoutpk - the owner and the name of the table, no matter if it has a primary key

ptable - the name of the parent table, is not null only if the table has a primary key

pcount - the number of records of the parent table

pkname - the name of the primary key constraint

pkstatus - the status of the primary key constraint

chtable, chowner - the name and the owner of the child table. They will be null if the parent table has no childs (foreign keys that references it). The owner is not necessarily the same with the owner of the parent.

fkname - the name of the foreign key constraint name

chcount - the number of records of the child table