rem rem Generate Foreign Key Creation DDL for everything pointing to a table rem rem Input : Tablename rem rem Chris Brown - Bedford, NS Canada rem set heading off pagesize 999 feedback off verify off spool create_fks_to_&1..sql REM REM SELECT 'ALTER TABLE '||fk.table_name||' ADD ('||chr(10)|| ' CONSTRAINT '||fk.constraint_name||chr(10)|| ' FOREIGN KEY'||chr(10)|| ' ('||fkcol1.COLUMN_NAME|| DECODE(fkcol2.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||fkcol2.COLUMN_NAME)|| DECODE(fkcol3.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||fkcol3.COLUMN_NAME)|| ')'||chr(10)|| ' REFERENCES &1'||chr(10)|| ' ('||pkcol1.COLUMN_NAME|| DECODE(fkcol2.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||pkcol2.COLUMN_NAME)|| DECODE(fkcol3.COLUMN_NAME, NULL, NULL,chr(10)||' ,'||pkcol3.COLUMN_NAME)|| '));' FROM user_constraints fk --fk to remember , user_cons_columns fkcol1 --col1 of fk to remember , user_cons_columns fkcol2 --col2 of fk to remember , user_cons_columns fkcol3 --col3 of fk to remember , user_constraints pk --pk constraint of table to set free , user_cons_columns pkcol1 --col1 of pk to remember , user_cons_columns pkcol2 --col2 of pk to remember , user_cons_columns pkcol3 --col3 of pk to remember WHERE fk.constraint_type = 'R' AND fk.r_owner = pk.owner AND fk.r_constraint_name = pk.constraint_name AND pk.table_name like UPPER('&1') AND fk.owner = fkcol1.owner AND fk.constraint_name = fkcol1.constraint_name AND 1 = fkcol1.position AND fk.owner = fkcol2.owner(+) AND fk.constraint_name = fkcol2.constraint_name(+) AND 2 = fkcol2.position(+) AND fk.owner = fkcol3.owner(+) AND fk.constraint_name = fkcol3.constraint_name(+) AND 3 = fkcol3.position(+) AND pk.owner = pkcol1.owner AND pk.constraint_name = pkcol1.constraint_name AND 1 = pkcol1.position AND pk.owner = pkcol2.owner(+) AND pk.constraint_name = pkcol2.constraint_name(+) AND 2 = pkcol2.position(+) AND pk.owner = pkcol3.owner(+) AND pk.constraint_name = pkcol3.constraint_name(+) AND 3 = pkcol3.position(+); REM REM spool off set heading on feedback on verify on prompt * Note: This script to recreate Foreign Keys pointing prompt * to '&1' is called 'create_fks_to_&1..sql' prompt prompt * Also note: This script can only handle up to three prompt * columns in a foreign key.