REM
/*
 * fkon-off.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 cty Prompt 'Enter the type of constraint (C=CK,R=FK,P=PK,U=UK,%=ALL): '
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 #
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

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 '||constraint_name||';'
FROM    user_constraints
WHERE   constraint_type LIKE UPPER('&&cty')
AND     table_name LIKE UPPER('&&tnm')
/
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 quickly disable, enable or drop constraints of a given type,
 *           for a given table/all tables. 
 *
 * DETAIL - this simple single table SQL*Plus script generates the necessary ALTER table commands to turn off, turn on
 *          (with or without validation) or even drop constraints on 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. Any type of constraint
 *          can be specified (C=CK,R=FK,P=PK,U=UK,%=ALL) although no dependency checking or order is applied so the disabling or dropping
 *          of PKs may not be successful if depdendent FKs still exist.
 *
 *          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