如何在 SQL Server 中将索引从一个表复制到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/652148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy indexes from one table to another in SQL Server
提问by Byron Whitlock
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?
有谁知道如何做到这一点？
回答by TFD
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
然后您可以使用 Microsoft SQL 管理工具中的“脚本”选项对索引、触发器等进行逆向工程
Simply right click on a table name in the SQL Management Studio table list and select "Script Table as" and then "Create to"
只需右键单击 SQL Management Studio 表列表中的表名称，然后选择“脚本表为”，然后选择“创建为”
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)
首先检查您是否将“工具/选项/SQL Server 对象资源管理器/脚本/脚本索引”设置为“真”。这在某些版本的 SQL 管理工具中设置为 false（感谢 Mark）
回答by Mark Sowul
By default the right-click table "CREATE" does not include the indexes or triggers, just the table definition and constraints.
默认情况下，右键单击表“CREATE”不包括索引或触发器，仅包括表定义和约束。
You can right click the databaseand 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.
编辑：这是默认设置，但正如 TFD 提到的，它可以更改，谢天谢地。
回答by Jarod Elliott
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.
我对 SQL Server 不是特别熟悉，但是基于数据库表在其他数据库中定义和使用的方式，我想说没有办法通过复制数据或使用 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.
执行此操作的唯一方法是在创建表后使用 ALTER TABLE 语句在 CREATE TABLE 语句期间。
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.
我相信会有工具可用于生成 ALTER TABLE 语句，以根据您已有的表创建所有适当的索引，使其简单可靠。
回答by cmsjr
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.
您可以使用 sysindexes（或 sys.indexes，具体取决于您的版本）中的信息构建脚本来重新创建所有索引，但使用 Select * into 方法也不会获取外键和主键或任何扩展属性. 如果您使用的是 2005+ 版本，您应该真正考虑使用 SSIS，如果您使用的是 2000，则应该考虑使用 DTS，两者都有向导来简化这种复制。
回答by Sam
Rightclick the index in SSMS and do script as > create .
右键单击 SSMS 中的索引并执行 script as > create 。
Change the table name and the index name and you should be set
更改表名和索引名，你应该设置
回答by Steve
You should get the idea here
你应该在这里明白
Sub CopyTemp(tblName As String, Optional WithIndex As Boolean = False)
Dim db As DAO.Database
Dim tDefOrig As DAO.TableDef
Dim tDefNew As DAO.TableDef
Dim idxOrig As DAO.Index
Dim idxNew As DAO.Index
Dim fld As DAO.Field
Dim tempName As String
tempName = "temp" & tblName
Set db = CurrentDb
db.Execute "Select * Into " & tempName & " From " & tblName
If WithIndex = True Then
Set tDefOrig = db.TableDefs(tblName)
Set tDefNew = db.TableDefs(tempName)
For Each idxOrig In tDefOrig.Indexes
Set idxNew = tDefNew.CreateIndex(idxOrig.Name)
With idxNew
For Each fld In idxOrig.Fields
.Fields.Append .CreateField(fld.Name)
Next
.Primary = idxOrig.Primary
.Unique = idxOrig.Unique
End With
tDefNew.Indexes.Append idxNew
tDefNew.Indexes.Refresh
Next
End If
End Sub
