drop a lot of constrains

[mybear]

I am dropping about 200 constarints of a particular user. I do not want to type this over and over again.

Therefore, I need a script that would generate
"alter table table_name drop constraint constrait_name"

Can anyone help?

[sambavan]

    SPOOL drop_constraint.sql

    SELECT 'alter table '||owner||'.'||table_name||
                ' drop constraint '|| constraint_name ||';'
      FROM DBA_CONSTRAINTS
      WHERE OWNER = ' your_name';

     SPOOL OFF;

    Note: If you only have the user privileges, then use 
            USER_CONSTRAINTS instead of DBA_CONSTRAINTS 
                view.

Now edit the drop_constraint.sql script and then execute it on the sqlplus...

           SQL> @drop_constraint

Hope this would help you.

Sam

[Edited by sambavan on 10-15-2001 at 01:39 PM]

[Shestakov]

select 'alter table '||c.owner||'.'||c.table_name||' drop constraints '||c.constraint_name||';'
from all_constraints c
where c.owner = 'YOUR_OWNER'
;

select 'alter table '||t.owner||'.'||t.table_name||' drop constraints '||c.constraint_name||';'
from all_tables t,
all_constraints c
where t.owner = c.owner and
t.table_name = c.table_name
and t.owner = 'YOUR_OWNER'
;