RE: Foreign Key constraint Script

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFF00B.3AC1D030
Content-Type: text/plain;

        charset="iso-8859-1"

The attached script will create two additional scripts, which contain the commands to disable/enable constraints for specified tables. It could use a bit of enhancement (the owner and table-list are hardcoded), but it gets the job done.

Cheers!

> -----Original Message-----
> From: Ashish Shah [mailto:ar_shah_at_yahoo.com]
> Sent: 17 July, 2000 11:32 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Foreign Key constraint Script
>
>
> Hi All,
>
> Does any one have any script that will tell me
> a Table has been referenced (foreign key) in how many
> other tables. I had a tough time droping a table
> since
> it was refered by so many other table.
>
> I tried looking user_constraints but it doesn't give
> you information about primary table.
>
> TIA.
------_=_NextPart_000_01BFF00B.3AC1D030
Content-Type: application/octet-stream;

        name="list_constraints.sql"
Content-Disposition: attachment;

        filename="list_constraints.sql"

set echo off
set pagesize 0
set termout off
set feedback off
set pause off
set line 1024
set trimspool on

spool disable_constraints.sql
select 'alter table ' || owner || '.' || table_name ||

       ' disable constraint ' || constraint_name || ';'    from all_constraints
   where owner = 'RSIPLATE'

     and constraint_type = 'R'
     and r_constraint_name in(select constraint_name from all_constraints
                                 where constraint_type = 'P'
                                   and owner = 'RSIPLATE'
                                   and table_name in('LICENSE_TYPE'));

spool enable_constraints.sql
select 'alter table ' || owner || '.' || table_name ||

       ' enable constraint ' || constraint_name || ';'    from all_constraints
   where owner = 'RSIPLATE'

     and constraint_type = 'R'
     and r_constraint_name in(select constraint_name from all_constraints
                                 where constraint_type = 'P'
                                   and owner = 'RSIPLATE'
                                   and table_name in('LICENSE_TYPE'));