Programmatically copy index from one table to another, via a stored procedure?

I use a stored procedure to copy the tables from one database to the other database. Right now it does

insert into copy 
   select * from original

I would like to copy the index from the original table as well. What would be the best way to go about this ?

This is a really complicated thing to do in SQL code. It's complicated because indexes have so many options. Potential issues/complications:

• Covering indexes

• Included fields

• Column order

• Filtered indexes

• Complicated index types (xml indexes, etc)

You will be much better off either scripting these out in SSMS or using the .NET SMO framework for this.

To appropriately account for all the above issues will require querying a large number of system tables and using CURSORs, WHILE loops, or other iterative constructs to get things in the right order.

It ain't pretty.

StackExchange Database Administrators Q#7977, answer score: 7