snipt

Ctrl+h for KB shortcuts
#11814

SQL

Drop all tables, constraints, and sequences within an Oracle schema

BEGIN

FOR c IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE ('DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS');
END LOOP;

FOR s IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE ('DROP SEQUENCE ' || s.sequence_name);
END LOOP;

END;
https://snipt.net/embed/a7a99d732c02d6ceea36d1ac22e33cfc/
https://snipt.net/raw/a7a99d732c02d6ceea36d1ac22e33cfc/
a7a99d732c02d6ceea36d1ac22e33cfc
sql
SQL
11
2014-04-19T18:06:54
True
False
/api/public/snipt/11814/
drop-all-tables-and-constraints-within-an-oracle-schema
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a></pre></div></td><td class="code"><div class="highlight"><pre><span id="L-1"><a name="L-1"></a><span class="k">BEGIN</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">FOR</span> <span class="k">c</span> <span class="k">IN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">table_name</span> <span class="k">FROM</span> <span class="n">user_tables</span><span class="p">)</span> <span class="n">LOOP</span> </span><span id="L-4"><a name="L-4"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="p">(</span><span class="s1">&#39;DROP TABLE &#39;</span> <span class="o">||</span> <span class="k">c</span><span class="p">.</span><span class="k">table_name</span> <span class="o">||</span> <span class="s1">&#39; CASCADE CONSTRAINTS&#39;</span><span class="p">);</span> </span><span id="L-5"><a name="L-5"></a><span class="k">END</span> <span class="n">LOOP</span><span class="p">;</span> </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a><span class="k">FOR</span> <span class="n">s</span> <span class="k">IN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">sequence_name</span> <span class="k">FROM</span> <span class="n">user_sequences</span><span class="p">)</span> <span class="n">LOOP</span> </span><span id="L-8"><a name="L-8"></a><span class="k">EXECUTE</span> <span class="k">IMMEDIATE</span> <span class="p">(</span><span class="s1">&#39;DROP SEQUENCE &#39;</span> <span class="o">||</span> <span class="n">s</span><span class="p">.</span><span class="n">sequence_name</span><span class="p">);</span> </span><span id="L-9"><a name="L-9"></a><span class="k">END</span> <span class="n">LOOP</span><span class="p">;</span> </span><span id="L-10"><a name="L-10"></a> </span><span id="L-11"><a name="L-11"></a><span class="k">END</span><span class="p">;</span> </span></pre></div> </td></tr></table>
db, drop, oracle, permissions, sql