Oracle Foreign Key info on table / user

primary key Foreign Key on a table



SQL> set serveroutput on
declare
cons varchar2(60);
r_cons varchar2(60);
type vc230 is table of varchar2(30) index by binary_integer;
type num is table of number index by binary_integer;
fk_coln vc230;
pk_coln vc230;
fk_pos num;
pk_pos num;
cnt number;
tab_name_with_fk varchar2(30);
tab_name_with_pk varchar2(30);
cursor main is
select constraint_name cons, r_constraint_name r_cons, table_name tab_name_with_fk
from user_constraints
where table_name = upper('&table_name_with_foreign_key')
and constraint_type = 'R';
begin
for cur in main loop
dbms_output.put_line(lpad('*', 80, '*'));
dbms_output.put_line('Reference Constraint_Name : ' || cur.r_cons);
dbms_output.put_line('Constraint_Name : ' || cur.cons);
select table_name into tab_name_with_pk
from user_constraints
where constraint_name = cur.r_cons;
declare
cursor c1 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.cons
order by position;
cursor c2 is
select substr(column_name, 1, 20) columnname, position
from user_cons_columns a
where a.constraint_name = cur.r_cons
order by position;
begin
cnt := 1;
for cur1 in c1 loop
fk_coln(cnt) := cur1.columnname;
fk_pos(cnt) := cur1.position;
cnt := cnt + 1;
end loop;
cnt := 1;
for cur2 in c2 loop
pk_coln(cnt) := cur2.columnname;
pk_pos(cnt) := cur2.position;
cnt := cnt + 1;
end loop;
dbms_output.put_line('Table Contains FK Is ' || rpad(cur.tab_name_with_fk, 15, ' ') ||
' Table Contains PK Is ' || rpad(tab_name_with_pk, 15, ' '));
for i in 1..cnt-1 loop
if (i != cnt-1) then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') ||
' Pos ' || rpad(to_char(fk_pos(i)), 2, ' ') || ' ' || ' Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
elsif i = cnt-1 then
dbms_output.put_line('Foreign Key ' || rpad(fk_coln(i), 15, ' ') || ' Pos ' ||
rpad(to_char(fk_pos(i)), 2, ' ') || ' References ' || 'Primary Key ' ||
rpad(pk_coln(i), 15, ' ') || ' Pos ' || rpad(to_char(pk_pos(i)), 2, ' '));
end if;
end loop;
dbms_output.put_line(lpad('*', 80, '*'));
end;
end loop;
end;
/


SQL> /
Enter value for table_name_with_foreign_key: EMP
old 16: where table_name = upper('&table_name_with_foreign_key')
new 16: where table_name = upper('EMP')
********************************************************************************
Reference Constraint_Name : PK_DEPT
Constraint_Name : FK_DEPTNO
Table Contains FK Is EMP Table Contains PK Is DEPT
Foreign Key DEPTNO Pos 1 References Primary Key DEPTNO Pos 1
********************************************************************************

PL/SQL procedure successfully completed.


====



SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT b.table_name, b.owner
FROM dba_constraints a,
dba_constraints b
WHERE b.r_constraint_name = a.constraint_name
AND b.r_owner = a.owner
AND a.constraint_type in ('P','U')
AND b.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')
union
SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
FROM (SELECT DISTINCT a.table_name, a.owner
FROM dba_constraints a,
dba_constraints b
WHERE a.r_constraint_name = b.constraint_name
AND a.r_owner = b.owner
AND b.constraint_type in ('P','U')
AND a.constraint_type = 'R'
AND b.owner = 'SCOTT')
-- AND b.table_name = 'DEPT')

SQL> /

DDL
--------------------------------------------------------------------------------

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") DISABLE