Forum Stats

  • 3,757,486 Users
  • 2,251,236 Discussions
  • 7,869,842 Comments

Discussions

I want to drop tables based on their references i.e FK and PK order. How to select all tables?

Albert Chao
Albert Chao Member Posts: 60 Green Ribbon
edited Sep 13, 2021 10:17AM in SQL & PL/SQL
set serveroutput on;
declare
lv_str varchar2(1000);
begin
for c in(select distinct a.table_name as table_name, b.table_name as parent_table_name
from
all_constraints a
left outer join all_constraints b on a.r_constraint_name = b.constraint_name and a.owner = b.owner) loop
lv_str :='DROP TABLE '||c.table_name;
--lv_str :='DROP TABLE '||c.parent_table_name;


dbms_output.put_line(lv_str);
end loop;
end;

Will this work?

Tagged:

Best Answers

  • KayK
    KayK Member Posts: 1,678 Bronze Crown
    Accepted Answer

    Hi 3GK,

    what have you tried so far ?

    Maybe something like this helps you

    declare
     lv_str varchar2(1000);
    begin
    for c in(select distinct a.table_name as table_name, 
                    b.table_name as parent_table_name ,
                    a.owner
               from all_constraints a, 
                    all_constraints b 
              where a.r_constraint_name = b.constraint_name 
                and a.constraint_type   = 'R'
                and b.constraint_type   = 'P'
                and a.owner             = b.owner   -- if parent and child belongs to the same schema
                and a.owner             = 'SCOTT'
           ) loop
     lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- ' || c.parent_table_name;
     dbms_output.put_Line (lv_str );
     end loop;
    end;
    /
    

    regards

    Kay

    btw please choose a more readable name for your avatar. You're more than a number. Have a look at this: Update Your Community Display Name and Avatar!

  • EdStevens
    EdStevens Member Posts: 28,453 Gold Crown
    edited Sep 15, 2021 3:08PM Accepted Answer

    @mathguy Thanks, I should have known. Just wasn't spending any time thinking it through.

    That said, the basic principle of my original could still apply. Just have statements to explicitly drop FK constraints in the script. One could even put them at the front end of the script. Shouldn't be too hard to write a script that writes the final script

    spool doit.sql
    set echo off feedback off head off trimspool on
    spool doit.sql
    select 'alter table '||
           owner ||
          '.' ||
           table_name ||
          ' drop constraint ' ||
           constraint_name ||
          ';'
    from dba_constraints
    where ....
    ;
    
    select 'drop table ' ||
           owner ||
          '.' ||
           table_name ||
          ';'
    from dba_tables
    where ....
    ;
    spo off
    


    Then do a sanity check on 'doit.sql' before running it.

    Above sample code may need some tweaking, not tested.

    Albert Chao
«1

Answers

  • KayK
    KayK Member Posts: 1,678 Bronze Crown
    Accepted Answer

    Hi 3GK,

    what have you tried so far ?

    Maybe something like this helps you

    declare
     lv_str varchar2(1000);
    begin
    for c in(select distinct a.table_name as table_name, 
                    b.table_name as parent_table_name ,
                    a.owner
               from all_constraints a, 
                    all_constraints b 
              where a.r_constraint_name = b.constraint_name 
                and a.constraint_type   = 'R'
                and b.constraint_type   = 'P'
                and a.owner             = b.owner   -- if parent and child belongs to the same schema
                and a.owner             = 'SCOTT'
           ) loop
     lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- ' || c.parent_table_name;
     dbms_output.put_Line (lv_str );
     end loop;
    end;
    /
    

    regards

    Kay

    btw please choose a more readable name for your avatar. You're more than a number. Have a look at this: Update Your Community Display Name and Avatar!

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @KayK Thanks but will this drop parent table as well because I am getting output as below

    DROP TABLE SYSTEM.MVIEW$_ADV_EXCEPTIONS; -- MVIEW$_ADV_LOG


    Sure Kayk will update my profile

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @KayK In one go if I want to drop the tables how to achieve that using your code ?

  • KayK
    KayK Member Posts: 1,678 Bronze Crown

    You can use execute immediate instead of dbms_output. BUT DON'T IT.

    Spool the result of your query into a script and double check it before you execute it.

    As you see above you get a "drop table system.somewhat ...".

    There is no warranty that sqlplus does what you WANT to to. sqlplpus only does only what you're coding and that may be incorrect.

  • User_H3J7U
    User_H3J7U Member Posts: 462 Bronze Trophy

    cascade constraints

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    Would it require to drop parent table separately as in your code it is commented out? So, do I have to edit the script if I want to delete the parent table as well and remove the comment and place it in place of the table name?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    Hi, @User_3GK1M

    Whenever you have a problem, please post a little sample tables, (CREATE TABLE, and, if necessary, INSERT statements) so the people who want to help you can re-create the problem and test their ideas. Also post the results you want (in this case, maybe a script containing DROP TABLE statements) and an explanation of how you get those resiults from the given tables. Always give your full Oracle version number (e.g. 18.4.0.0.0).

    Are you trying to drop tables in order such that if table A has a foreign key reference to table B, then A is dropped before B? That may not be possible, since two tables can reference each other. Drop the foreign key constraints separately (in any order) first, then drop the tables (in any order).

    What do primary keys have to do with this problem?

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @Frank Kulash Is this code achieves the requirement by KayK

    declare
     lv_str varchar2(1000);
    begin
    for c in(select distinct a.table_name as table_name, 
                    b.table_name as parent_table_name ,
                    a.owner
               from all_constraints a, 
                    all_constraints b 
              where a.r_constraint_name = b.constraint_name 
                and a.constraint_type   = 'R'
                and b.constraint_type   = 'P'
                and a.owner             = b.owner   -- if parent and child belongs to the same schema
                and a.owner             = 'SCOTT'
           ) loop
     lv_str :='DROP TABLE ' || c.owner || '.' || c.table_name || '; -- ' || c.parent_table_name;
     dbms_output.put_Line (lv_str );
     end loop;
    end;
    /
    

    But why c.parent_table_name is commented out. Since we have to delete the tables so would it not require?

  • KayK
    KayK Member Posts: 1,678 Bronze Crown
    edited Sep 13, 2021 12:19PM

    My approach only drops the child tables because i thought that was your requirement.

    My comment only shows the related parent-table in the output, it doesn't drop them.

    So the real question is, what is your exact requirement ?

    drop all tables ?

    drop all tables including child tables ?

    drop only child tables ?

    What happens if exists parent - child - grandchildren tables ?

    Or self referencing tables ?

    What about to drop the whole schema and then recreate it with an existing create-script ?

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @KayK My requirement is to drop all tables in order of their FK and PK references