Lists foreign key(s) on a table
Using the following anonymous store procedure one can list the foreign key on a table and it’s column. It also recursively traverses tables to see if there are other tables dependent on it’s primary key.
set serveroutput on format wrapped
DECLARE
l_vc2_table_owner VARCHAR2(30) := upper('&1');
l_vc2_table_name VARCHAR2(30) := upper('&2');
PROCEDURE get_ind_columns(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2, l_vc2_pad IN VARCHAR2
, l_vc2_out_table_key OUT VARCHAR2)
IS
CURSOR l_cur_ind_columns IS
SELECT column_name FROM dba_ind_columns
WHERE index_owner = l_vc2_owner AND index_name = l_vc2_index_name
ORDER BY column_position;
l_vc2_columns VARCHAR2(100);
BEGIN
FOR l_rec IN l_cur_ind_columns
LOOP
IF l_vc2_columns IS NULL THEN
l_vc2_columns := l_rec.column_name;
ELSE
l_vc2_columns := l_vc2_columns || ' ' || l_rec.column_name;
END IF;
END LOOP;
l_vc2_out_table_key := ' (' || l_vc2_columns || ')';
END;
PROCEDURE print_pk(l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2, l_vc2_pad IN VARCHAR2) IS
CURSOR l_cur_main IS
SELECT di.owner, di.index_name
FROM dba_indexes di, dba_constraints dc
WHERE di.table_owner = l_vc2_table_owner and di.table_name = l_vc2_table_name
AND dc.owner = di.owner and dc.constraint_name = di.index_name
AND di.uniqueness = 'UNIQUE';
l_rec_main l_cur_main%ROWTYPE;
l_vc2_ind_columns VARCHAR2(1000);
FUNCTION check_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2)
RETURN BOOLEAN
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
l_b_status BOOLEAN;
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
l_b_status := TRUE;
ELSE
l_b_status := FALSE;
END IF;
CLOSE l_cur_fk;
RETURN l_b_status;
END;
PROCEDURE print_fk(l_vc2_owner IN VARCHAR2, l_vc2_index_name IN VARCHAR2,
l_vc2_table_owner IN VARCHAR2, l_vc2_table_name IN VARCHAR2,
l_vc2_pad IN VARCHAR2)
IS
CURSOR l_cur_fk IS
SELECT dc.owner, dc.table_name, dc.constraint_name
FROM dba_constraints dc, dba_cons_columns dcc
WHERE dc.r_owner = l_vc2_owner AND dc.r_constraint_name = l_vc2_index_name
AND dc.owner = dcc.owner AND dc.constraint_name = dcc.constraint_name;
l_rec l_cur_fk%rowtype;
CURSOR l_cur_dcc(l_vc2_owner IN VARCHAR2, l_vc2_constraint_name IN VARCHAR2) IS
SELECT column_name FROM dba_cons_columns
WHERE owner = l_vc2_owner AND constraint_name = l_vc2_constraint_name
ORDER BY position;
l_vc2_cons_columns VARCHAR(500);
BEGIN
OPEN l_cur_fk;
FETCH l_cur_fk INTO l_rec;
IF l_cur_fk%FOUND THEN
WHILE l_cur_fk%FOUND
LOOP
l_vc2_cons_columns := '';
FOR l_rec_dcc IN l_cur_dcc(l_rec.owner, l_rec.constraint_name)
LOOP
IF l_vc2_cons_columns IS NULL THEN
l_vc2_cons_columns := l_rec_dcc.column_name;
ELSE
l_vc2_cons_columns := l_vc2_cons_columns || ' ' || l_rec_dcc.column_name;
END IF;
END LOOP;
dbms_output.put_line(l_vc2_pad || ' Table FK: ' || l_rec.owner || '.' || l_rec.table_name
— || ' ' || l_rec.constraint_name
|| ' (' || l_vc2_cons_columns || ')');
print_pk(l_rec.owner, l_rec.table_name, l_vc2_pad || ' ');
FETCH l_cur_fk INTO l_rec;
END LOOP;
END IF;
CLOSE l_cur_fk;
END;
BEGIN
OPEN l_cur_main;
FETCH l_cur_main INTO l_rec_main;
IF l_cur_main%FOUND THEN
WHILE l_cur_main%FOUND
LOOP
IF check_fk(l_rec_main.owner, l_rec_main.index_name) THEN
l_vc2_ind_columns := '';
get_ind_columns(l_rec_main.owner, l_rec_main.index_name, l_vc2_pad, l_vc2_ind_columns);
dbms_output.put_line(l_vc2_pad || 'Table: ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' ' || l_vc2_ind_columns);
print_fk(l_rec_main.owner, l_rec_main.index_name, l_vc2_table_owner, l_vc2_table_name, l_vc2_pad);
END IF;
FETCH l_cur_main INTO l_rec_main;
END LOOP;
END IF;
END;
BEGIN
print_pk(l_vc2_table_owner, l_vc2_table_name, '');
END;
/
Output:
Table: SCOTT.TABLE1 (TABLE1_KEY)
Table FK: SCOTT.TABLE2 (TABLE2_FKEY)
Table: SCOTT.TABLE2 (TABLE2_KEY)
Table FK: SCOTT.TABLE3 (TABLE3_FK)