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.
How do I see all foreign keys to a table or column?
The following query will solve our problem, execute it as a sys user and check the output.
Display all the tables with foreign key
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' ;How do I find a foreign key in a table?
You get the Output from the above query for the SCOTT user.
Read - Querying Tablespace Usage
Connect with me on:
Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube: https://www.youtube.com/ocptechnology