Wednesday, September 6, 2006

Details of all foreign keys of a schema

DECLARE
PROCEDURE fk_details (powner VARCHAR2) IS
CURSOR c1 IS
SELECT t.table_name tbl
FROM dba_tables t
WHERE t.owner = powner;

CURSOR c2 (powner VARCHAR2, lptable VARCHAR2) IS
SELECT a.tt tt, a.owner o, b.table_name tn1, a.constraint_name cn1,
b.column_name col1, b.POSITION, a.r_constraint_name rcn,
c.column_name col2, c.POSITION p, c.table_name r_table_name,
a.r_owner ron
FROM (SELECT owner, constraint_name, r_constraint_name, r_owner,
1 tt
FROM dba_constraints
WHERE owner = UPPER (powner)
AND table_name = UPPER (lptable)
AND constraint_type != 'C'
UNION
SELECT owner, constraint_name, r_constraint_name, r_owner, 2
FROM dba_constraints
WHERE (r_constraint_name, r_owner) IN (
SELECT constraint_name, owner
FROM dba_constraints
WHERE owner = UPPER (powner)
AND table_name = UPPER (lptable))) a,
dba_cons_columns b,
dba_cons_columns c
WHERE b.constraint_name = a.constraint_name
AND b.owner = a.owner
AND c.constraint_name = a.r_constraint_name
AND c.owner = a.r_owner
AND b.POSITION = c.POSITION;
BEGIN
FOR rec1 IN c1 LOOP
FOR rec2 IN c2 (powner, rec1.tbl) LOOP
DBMS_OUTPUT.put_line ( rec2.tt
|| ','
|| rec2.o
|| ','
|| rec2.tn1
|| ','
|| rec2.cn1
|| ','
|| rec2.col1
);
END LOOP;
END LOOP;
END fk_details;
BEGIN
fk_details ('SCOTT');
END;

No comments:

Post a Comment