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?
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.
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
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.
Rightclick the index in SSMS and do script as > create .
Change the table name and the index name and you should be set