How to create a Drop Constraint script without knowing the name? 1

[hawspipe]

Hi All,
I sure hope someone will be able to help me with this issue, first a little background.
I work for a software company and the programmer that used to write the database scripts did not use a standardard naming convention for the foreign key constraints, they changed from one verstion to another. I have since standardize them, however, this only works for the new customers, not the existing ones.
I would like to send our existing customers a script that would create another script that would drop the foreign key constraints in their database, then I can have them add new constraints with the standardize names.
I have found out the following script gets me the correct information, but I can't figure how to create the script itself -

select table_name, constraint_name 
  from dba_constraints 
 where owner = 'TEST4'
   and constraint_type = 'R'
   and constraint_name like 'RI%'

I had though about using the "utl_file" but I can't guarentee that the customers will have their system setup correctly.
I would like the script (results) to look something like this -

Alter table ADJUSTMENT_CODES Drop Constraint RI_REF_1373623_1;
Alter table VENDOR_CONTRACTS Drop Constraint RI_REF_1612467_5;
Alter table RECEIVING_ADJUSTMENTS Drop Constraint RI_REF_1612469_5;
Alter table RECEIVING_DETAIL_ADJUSTS Drop Constraint RI_REF_1612470_4;

Does anyone have any ideas?
Thank you for your time,
Trey

 

[SantaMufasa]

Pipe,

I wrote the following so that your customer would not need to worry about the idiosyncracies of "utl_file"...This code just spools to, and executes from, their local machine:

set echo off
set pagesize 0
set trimspool on
set linesize 200
set feedback off
spool temp.sql
select 'Alter table '||table_name||' Drop Constraint '||constraint_name||';'
from dba_constraints
 where owner = 'TEST4'
   and constraint_type = 'R'
   and constraint_name like 'RI%';
spool off
prompt
prompt 'Wrote file "temp.sql". To execute from the SQL*Plus prompt, "@temp".'
prompt
set feedback on
set pagesize 35

Let us know if this does what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:05 (08Oct04) UTC (aka "GMT" and "Zulu"), 11:05 (08Oct04) Mountain Time)

 

[hawspipe]

Outstanding!!! It worked great, I think I was trying to make it harder than it actually was.
Thanks again.