drop a lot of constrains
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: drop a lot of constrains

  1. #1
    Join Date
    Jan 2001
    Posts
    72

    Cool

    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?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Code:
        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...

    Code:
               SQL> @drop_constraint

    Hope this would help you.

    Sam

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



    Life is a journey, not a destination!


  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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'
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width