Pastable version of Drop all Indexes in SQL Server

<div style="overflow:auto;border:solid 1px #ccc;background:#000;color:#F8F8F8">
          <div class="section">
            <span style="margin:0;padding:4px 6px;font-size:12px;float:right;">Transact-SQL</span>
            <pre style="float:left;margin:0 10px;border-right:0;color:#666;">1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31</pre>
            <pre class="sunburst">
Declare @Index <span style="color:#99CF50;">varchar</span>(<span style="color:#3387CC;">128</span>)
Declare @Table <span style="color:#99CF50;">varchar</span>(<span style="color:#3387CC;">128</span>)

<span style="color:#E28964;">Select</span>
SysIndexes.Name As <span style="color:#65B042;"><span style="color:#65B042;">'</span>Index'</span>,
SysObjects.Name As <span style="color:#65B042;"><span style="color:#65B042;">'</span>Table'</span>
Into
<span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes</span>
<span style="color:#E28964;">From</span>
SysIndexes
<span style="color:#E28964;">Inner Join</span> SysObjects On
	SysObjects.id = SysIndexes.id
<span style="color:#E28964;">Where</span>
SysIndexes.Name Is <span style="color:#E28964;">Not Null</span>
<span style="color:#E28964;">and</span> SysObjects.XType = <span style="color:#65B042;"><span style="color:#65B042;">'</span>U'</span>
<span style="color:#E28964;">Order By</span>
SysIndexes.Name,
SysObjects.Name

While (<span style="color:#E28964;">Select</span> Count(*) <span style="color:#E28964;">From</span> <span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes) &gt; 0</span>
<span style="color:#E28964;">Begin</span>
	<span style="color:#E28964;">Set</span> @Index = (<span style="color:#E28964;">Select</span> Top <span style="color:#3387CC;">1</span> [Index] <span style="color:#E28964;">From</span> <span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes)</span>
	<span style="color:#E28964;">Set</span> @Table = (<span style="color:#E28964;">Select</span> Top <span style="color:#3387CC;">1</span> [Table] <span style="color:#E28964;">From</span> <span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes)</span>

<span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">--</span>Print 'Drop Index [' + @Index + '] On [' + @Table + ']' + Char(13)</span>
	Exec (<span style="color:#65B042;"><span style="color:#65B042;">'</span>Drop Index ['</span> + @Index + <span style="color:#65B042;"><span style="color:#65B042;">'</span>] On ['</span> + @Table + <span style="color:#65B042;"><span style="color:#65B042;">'</span>]'</span>)
	<span style="color:#E28964;">Delete</span> <span style="color:#E28964;">From</span> <span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes Where [Index] = @Index and [Table] = @Table</span>
End

<span style="color:#E28964;">Drop</span> <span style="color:#E28964;">Table</span> <span style="color:#AEAEAE;font-style:italic;"><span style="color:#AEAEAE;font-style:italic;">#</span>Indexes</span>
</pre>
          </div>
        </div>

<a href="http://www.refactormycode.com/codes/522-drop-all-indexes-in-sql-server" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>