Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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 ?

share|improve this question
Index is not just data - it's a database object. So you're really talking about a vastly different operation - not just copying across data from one table to another, but also structure from the old database to the new. This requires you to "inspect" the old index definition and create a new index on the new table to match the old one – marc_s Nov 16 '11 at 15:37
1  
While you can definitely use a stored procedure to move data from old to new, I would not recommend doing the same with structural database objects, like tables, indexes, and so on.. if you need them in your new database - script out their definition from the old and run that script once on your new table – marc_s Nov 16 '11 at 15:40
1  
+1 @marc_s - You can move data with your SELECT * script but you won't catch things like keys, constraints, etc – JNK♦ Nov 16 '11 at 15:42
1  
@marc_s - I believe replication will also cover all data, constraints, and indexes for a published table. – Nick Chammas Nov 16 '11 at 16:00
What's the impetus for this and how often do you want to do this? Do the copies need to stay in sync somehow? – Nick Chammas Nov 16 '11 at 16:04

migrated from stackoverflow.com Nov 16 '11 at 15:58

1 Answer

up vote 7 down vote accepted

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.

share|improve this answer
Fortunately, I use .net framework and I ended up using the .NET SMO. Thanks for pointing me in right direction. – Joker Dec 8 '11 at 7:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.