Title: Creating a DDL Script for Foreign Key Constraints in A Schema

Author: Manmohan Jalsingh, an Oracle DBA for Hi Tech Consultants Inc, in Southfield,
Michigan.

Use this script to generate a DDL script to create foreign keys in an entire schema, or for a specific table in given schema.

rem
rem
rem Script: DDLRCONS.SQL
rem Written By: Manmohan Jalsingh
rem Date: 3/10/1999
rem

set serveroutput on size 1000000
set feed off veri off trims off lines 80

Accept l_schema prompt "Enter Schema :"
Accept l_table  prompt "Enter Table (ENTER for all tables) :"

prompt

declare
        cursor cur_cons is
                select owner, table_name, constraint_name,
                r_owner,r_constraint_name,delete_rule
                from dba_constraints
                where owner=upper('&l_schema')
                and table_name=nvl(upper('&l_table'),table_name)
                and constraint_type = 'R';

        cursor cur_parent (i_owner in varchar2, i_constraint in varchar2) is
                select table_name
                from dba_constraints
                where owner = i_owner
                and constraint_name = i_constraint;

        cursor cur_conscol (i_owner in varchar2, i_constraint in varchar2) is
                select position, column_name

                from dba_cons_columns
                where owner = i_owner
                and   constraint_name = i_constraint
                order by position;

        l_column dba_cons_columns.column_name%type;
        l_position dba_cons_columns.position%type;
        l_table_name dba_constraints.table_name%type;
        l_delete_rule varchar2(30);

begin
        for rec_cons_cur in cur_cons
        loop
                dbms_output.put_line ('alter table '||
                                rec_cons_cur.owner||'.'||rec_cons_cur.table_name);
                dbms_output.put_line ('add '||
                                'constraint '||rec_cons_cur.constraint_name||
                                ' foreign key ');
                dbms_output.put('(');
                open cur_conscol(rec_cons_cur.owner,rec_cons_cur.constraint_name);
                loop
                        fetch cur_conscol into l_position,l_column;
                        exit when       cur_conscol%NOTFOUND;
                        if l_position > 1 then
                                dbms_output.put(','||l_column);
                        else
                                dbms_output.put(l_column);
                        end if;
                end loop;
                close cur_conscol;
                dbms_output.put_line(')');

                open cur_parent (rec_cons_cur.r_owner,rec_cons_cur.r_constraint_name);
                fetch cur_parent into l_table_name;
                close cur_parent;

                select decode(rec_cons_cur.delete_rule,
                        'CASCADE',' on delete cascade;',
                        'NO ACTION',';',
                        ';')
                into l_delete_rule
                from dual;

                dbms_output.put_line ('references '||
                        rec_cons_cur.r_owner||'.'||l_table_name||l_delete_rule);

                dbms_output.put(chr(10));
        end loop;
end;
/
prompt