Create a gist now

Instantly share code, notes, and snippets.

@kekru /SQL utils.md
Last active Oct 23, 2016

What would you like to do?

#DB2
##Create drop statements for all tables:
select 'drop table '||rtrim(tabschema)||'.'||rtrim(tabname)||';' from syscat.tables where tabschema = 'MYSCHEMA';

#Oracle ##Delete all sequences and tables http://stackoverflow.com/questions/2549718/dropping-all-user-tables-sequences-in-oracle

BEGIN

  --Bye Sequences!
  FOR i IN (SELECT us.sequence_name
              FROM USER_SEQUENCES us) LOOP
    EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';
  END LOOP;

  --Bye Tables!
  FOR i IN (SELECT ut.table_name
              FROM USER_TABLES ut) LOOP
    EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';
  END LOOP;

END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment