Avi Zurel - @KensoDev

Delete all tables in sql database

Posted on May 25, 2009

Hi all,

Recently I encountered a bizarre request, I had to delete all the tables from an Sql database.

The reason is even more bizarre. The client had a system auto creating them without making sure if they exist first. Ao, I had to write some code to first delete all the tables.

I thought maybe other people like me may encounter the same issue, so I wrote some code for it.

The code can either print out the Drop statements for each table or execute it.

Good luck!

Tested on sql server 2005

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum2" style="color: #606060;">   2:</span> <span style="color: #0000ff;">DECLARE</span> DropSequence <span style="color: #0000ff;">CURSOR</span> <span style="color: #0000ff;">LOCAL</span> FAST_FORWARD</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum3" style="color: #606060;">   3:</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum4" style="color: #606060;">   4:</span> <span style="color: #0000ff;">FOR</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum5" style="color: #606060;">   5:</span>     <span style="color: #0000ff;">SELECT</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum6" style="color: #606060;">   6:</span>         N<span style="color: #006080;">'DROP TABLE '</span> + QUOTENAME(TABLE_SCHEMA) + N<span style="color: #006080;">'.'</span> + QUOTENAME(TABLE_NAME)</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum7" style="color: #606060;">   7:</span>     <span style="color: #0000ff;">FROM</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum8" style="color: #606060;">   8:</span>         INFORMATION_SCHEMA.TABLES</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum9" style="color: #606060;">   9:</span>     <span style="color: #0000ff;">WHERE</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum10" style="color: #606060;">  10:</span>         TABLE_TYPE = <span style="color: #006080;">'BASE TABLE'</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum11" style="color: #606060;">  11:</span>             <span style="color: #0000ff;">AND</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum12" style="color: #606060;">  12:</span>         OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N<span style="color: #006080;">'.'</span> + QUOTENAME(TABLE_NAME)), <span style="color: #006080;">'IsMSShipped'</span>) = 0</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum13" style="color: #606060;">  13:</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum14" style="color: #606060;">  14:</span>     <span style="color: #0000ff;">OPEN</span> DropSequence</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum15" style="color: #606060;">  15:</span>     <span style="color: #0000ff;">WHILE</span> 1 = 1</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum16" style="color: #606060;">  16:</span>         <span style="color: #0000ff;">BEGIN</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum17" style="color: #606060;">  17:</span>             <span style="color: #0000ff;">FETCH</span> <span style="color: #0000ff;">NEXT</span> <span style="color: #0000ff;">FROM</span> DropSequence <span style="color: #0000ff;">INTO</span> @dropSql</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum18" style="color: #606060;">  18:</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum19" style="color: #606060;">  19:</span>                 <span style="color: #0000ff;">IF</span> <span style="color: #cc6633;">@@FETCH_STATUS</span> &lt;&gt; 0 <span style="color: #0000ff;">BREAK</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum20" style="color: #606060;">  20:</span>                     <span style="color: #0000ff;">RAISERROR</span> (@dropSql , 0, 1) <span style="color: #0000ff;">WITH</span> NOWAIT</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum21" style="color: #606060;">  21:</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum22" style="color: #606060;">  22:</span>             --<span style="color: #0000ff;">EXEC</span>(@dropSql )</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum23" style="color: #606060;">  23:</span>             <span style="color: #0000ff;">PRINT</span> @dropSql</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum24" style="color: #606060;">  24:</span>         <span style="color: #0000ff;">END</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum25" style="color: #606060;">  25:</span></pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: #f4f4f4; text-align: left;"><span id="lnum26" style="color: #606060;">  26:</span> <span style="color: #0000ff;">CLOSE</span> DropSequence</pre>

<p>
  <!--CRLF-->
</p>

<pre style="padding: 0px; font-size: 8pt; margin: 0em; overflow: visible; width: 100%; color: black; direction: ltr; border-style: none; line-height: 12pt; font-family: 'Courier New',courier,monospace; background-color: white; text-align: left;"><span id="lnum27" style="color: #606060;">  27:</span> <span style="color: #0000ff;">DEALLOCATE</span> DropSequence</pre>

<p>
  <!--CRLF-->
</p>