The heart of the procedure comes next.  The list of tables in the current database is gathered into the temp table created for that purpose.  You can also modify this where clause to return only the set of tables that you want.  A second cursor is then declared for this table.  You'll notice the use of local variables to hold the contents of @@fetch_status.  This was needed because as you will note we now have a cursor running inside of another cursor. 

A list of indexes is then gathered into the second temp table for the table that is currently being processed.  I then declare a cursor on this temp table to loop over the table.  (This is nesting level three for the cursors.)  For each trip through the loop, a drop index statement is created and then executed against the destination server. 

Once the indexes have been dropped, we are now ready to move the data.  The destination table is truncated.  The data is then BCPed out of the source table into a file on the source server.  This data is then BCPed into the destination table and the file is deleted from the source server.

The next block of really nasty looking code is needed to reverse engineer the indexes.   There are quite a few things to note about the system tables, sysindexes in particular, that need to be accounted for.  The status column is one of those complete violations of 3N form that are so familiar in the system tables.  Whether an index is unique or not is specified in the status column.  status & 2 != 0 tells us that this is a unique index and an indid value of 1 designates a clustered index.   From here I simply apply the index_col function to get the columns participating in the index.  This needs to be done 16 times as there can be 16 columns in the index.   The index_col function needs to be used, because the indexes are entered in sysindexes in another of those columns that violate every sense of good database design.   If anyone knows how to parse the column names or numbers out of that column using bitwise operators, please drop me a line.  Now looking at this, you might ask why I used 19 variables.  These are there simply due to the 255 character constraint.   It also breaks things up a little better.  The last thing to note about this is the fact that sysindexes contains more than just indexes.  (Why is some nebulous reason only Microsoft knows.)  So, since you have more than just indexes in a table that is should hold only indexes, filtering by an indid between 1 and 250 gives us only the indexes.  I encourage everyone to run the following statement after this code block:  select @var1 + @var2 + @var3 + @var4 +  @var5 +  @var6 +   @var7 +  @var8 +  @var9 +  @var10 +  @var11 +  @var12 +   @var13 +  @var14 +  @var15 +  @var16 +  @var17 +  @var18 +   @var19.  This will show you the select statement that is finally built and then executed.  The result is dumped into the createindexes table via an insert...exec statement.  Now you'll notice that while this table is only used in this procedure, it can not be a temporary table.  This is because I have to BCP the contents of this table out to a file.  Why?  The same 255 character limit I'll be very glad to see disappear in 7.0.  The table is then truncated.

Naming Conventions 1 2 3