tables with foreign key

Display all the tables with foreign key

Hello, friends in this article we are going to discuss how to display all the tables in a schema with foreign key relations and the number of records for the parent and the child tables.

Import view only using impdp Click here to read

The following query will solve our problem, execute it as a sys user and check the output.

col powner for a10
col TABLESWITHOUTPK for a15
col chcount for a10
col ptable for a15
col chtable for a10
col chowner for a10
col fkname for a15
set line 300
 
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' ;
 

Output from above query for SCOTT user.

tables with foreign key

Check my youtube click here

Share this
Share
Share