REM
/*
 * FKOn-OffDepend.sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
ACCEPT mde Prompt 'Enter ON to enable, OFF to disable, DROP to remove: '
ACCEPT tnm Prompt 'Enter a table name (%=ALL): '
ACCEPT nov Prompt 'Enter NO for NOVALIDATE when enabling otherwise leave blank: '

SET ECHO off PAGES 0 HEAD off FEEDBACK off VERIFY off TERMOUT off

SPOOL fk-&&mde..tmp
PROMPT WHENEVER SQLERROR EXIT FAILURE
PROMPT SET DEF # ECHO off VER off FEEDBACK off
COLUMN x PRINT word
SELECT 'BEGIN IF user!='''||user||''' OR ''##_CONNECT_IDENTIFIER''!=''&&_CONNECT_IDENTIFIER'' THEN RAISE_APPLICATION_ERROR(-20000,''You are not in the right account or the correct database'');END IF;END;'||CHR(10)||'/' X
FROM dual;
PROMPT SET DEF on ECHO off FEEDBACK on

PROMPT SELECT constraint_type,status,COUNT(*) FROM user_constraints GROUP BY constraint_type,status;;
SELECT  'ALTER TABLE '||LOWER(user)||'.'||LOWER(table_name)||' '||
        DECODE(UPPER('&&mde'),'ON','ENABLE &&nov.VALIDATE','OFF','DISABLE','DROP','DROP','x')||
        ' constraint '||a.constraint_name||';'
FROM    user_constraints a
WHERE   constraint_type ='R'
AND     a.r_constraint_name IN (SELECT b.constraint_name
				FROM user_constraints b
				WHERE b.table_name LIKE UPPER('&&tnm')
				AND (b.constraint_type='P' OR b.constraint_type='U')
				)
/
PROMPT SELECT constraint_type,status,COUNT(*) FROM user_constraints GROUP BY constraint_type,status;;
SPOOL off
SET PAGES 0 HEAD off FEEDBACK on VERIFY on TERMOUT on
PROMPT You now have a script file called fk-&&mde..tmp to run as required
UNDEF mde
UNDEF cty
UNDEF tnm
SET ECHO on


SET DOC off
/* SUMMARY - Quickly generate a script that can disable, enable or drop foreign key constraints, that reference a 
 *           given table(s).
 *
 * DETAIL - this simple SQL*Plus script generates the necessary ALTER table commands to turn off, turn on
 *          (with or without validation) or even drop foreign key constraints on all tables that make reference to a specified table,
 *          wildcard tablename match or all tables for the current schema. When re-enabling a choice of with or without validation is 
 *          available. 
 *
 *          As a security measure against running the resulting output against the wrong schema user or database a simple check is added
 *          before anything is run to make sure the same user as the script was generated for is being used and that the same database is
 *          also connected to as when the script was genereted. Note that this latter part uses an Oracle SQL*Plus 9 and above feaure so you may
 *          need to remove it if you are on Oracle8 or below.
 *          
 *          A count of the constriants by type and status is also given before and after the resultant ALTER TABLE commands
 *          is included so the ultimate impact of the script can be seen.
 */
REM