How to copy indexes from one table to another in SQL Server

Question

I need to copy the indexes from one table to another. There are a LOT of indexes and I don't want to recreate them from scratch. Seems error prone anyways.

I have copied the structure using

SELECT * INTO [BackupTable] FROM [OriginalTable]

But that doesn't copy indexes, constraints, triggers etc

Does anyone know how to do this?

Solution 1

Do you want to copy the Index definition?

Then you can reverse engineer the index, triggers etc using the "Script" option in the Microsoft SQL Management tool

Simply right click on a table name in the SQL Management Studio table list and select "Script Table as" and then "Create to"

You can't copy the Index data as it relates to the physical storage of the Index

First check that you have "Tools/Options/SQL Server Object Explorer/Scripting/Script Indexes" set to "True". This is set to false in some version of the SQL Management tool (thanks Mark)

Solution 2

By default the right-click table "CREATE" does not include the indexes or triggers, just the table definition and constraints.

You can right click the database and click "Tasks" -> "Generate Scripts" which will allow you to do this

Edit: this is the default but as TFD mentions it can be changed, thankfully.

Solution 3

I'm not specifically familiar with SQL Server, however based on the way database tables are defined and used in other databases, i would say there is no way to do this by just copying the data or using a SELECT INTO.

The indexes need to be defined as part of the table structure and need to be generated for any existing data that may be in the table.

The only way to do this is during the CREATE TABLE statement of by using an ALTER TABLE statement after the table has been created.

Statements for working with the data itself are separate to working with the table definitions so i don't think there will be any work around or shortcut for this.

I'm sure there would be tools available to generate the ALTER TABLE statement to create all the appropriate indexes based on the table you already have, making it easy and reliable.

Solution 4

Rightclick the index in SSMS and do script as > create .

Change the table name and the index name and you should be set

Solution 5

You could build a script using the information in sysindexes (or sys.indexes depending on your version) to recreate all of the indexes, but using the Select * into approach is also not going to pick up up foreign and primary keys or any extended proprties. You should really look into using SSIS if you are using a 2005+ version, or DTS if you are using 2000, both have wizards to simplify this kind of copy.

SSIS Import Export

DTS Import Export

View more solutions

Comments

[TFD]

-1 Only if you have "Tools/Options/SQL Server Object Explorer/Scripting/Script Indexes" set to "False". Is this a default in the new release?

[Mark Sowul]

Well how about that. It is evidently the default in 2005 and 2008 R2 (I certainly didn't change it), and I am very happy to be able to do it more easily.

[Michael L.]

Welcome to SO! Thanks for taking the time to answer another users' question; however, please read how to answer and make sure you're answering the question that is being asked. Unfortunately, this answer is not very helpful since the OP specifically asked a SQL server question and didn't say anything about using any programming language or library, so this answer may not make any sense to the author or apply to their situation.