如何通过 SQL Server 使用 SMO 删除并重新创建主键索引？

Question

我正在使用 SQL Server 2005 Express。我想使用 SMO 循环遍历数据库中的每个表，并将每个 Char 列更改为 Varchar 列。如果列是主键的成员，我需要在更改列的数据类型之前首先删除主键。然后我需要重新创建索引。这是我尝试使用的代码：

foreach (Table table in database.Tables)
{
    Index pk = table.Indexes.Cast<Index>().SingleOrDefault(index => index.IndexKeyType == IndexKeyType.DriPrimaryKey);
    if (pk != null)
    {
        pk.Drop();
        table.Alter();
    }
    foreach (Column column in table.Columns.Cast<Column>().Where(column => column.DataType.SqlDataType == SqlDataType.Char))
    {
        column.DataType = new DataType(SqlDataType.VarChar, column.DataType.MaximumLength);
    }
    table.Alter();
    if (pk != null)
    {
        pk.Create();
    }
}

但是当我尝试创建索引时，出现异常，并显示消息“无法访问 Microsoft.SqlServer.Management.Smo.Index '[PK_table1]' 的属性或方法，因为它已被丢弃。”那么有没有一个好的方法来完成我想要用 SMO 做的事情呢？

在使用 Index 的 Script 方法删除索引之前，我尝试编写索引脚本，但它抛出异常并显示消息“索引 'PK_table1' 引用不存在的列 '[table1].[owner]'”。所有者列显然存在。

Answer 1

我能够删除主键，更改列数据类型，并使用以下代码重新创建主键：

// using System.Collections.Specialized;

foreach (Table table in database.Tables)
{
    // object to hold the index script
    StringCollection pk_script = new StringCollection();

    Index pk = table.Indexes.Cast<Index>().SingleOrDefault(index => index.IndexKeyType == IndexKeyType.DriPrimaryKey);
    if (pk != null)
    {
        // script the index
        pk_script = pk.Script();
        pk.Drop();
        table.Alter();
    }
    foreach (Column column in table.Columns.Cast<Column>().Where(column => column.DataType.SqlDataType == SqlDataType.Char))
    {
        column.DataType = new DataType(SqlDataType.VarChar, column.DataType.MaximumLength);
    }
    table.Alter();

    // iterate through script StringCollection
    foreach (String tsql in pk_script)
    {
        database.ExecuteNonQuery(tsql);
    }                
} 

一些警告：

1. 定义 pk 的行将抛出
   如果有表则例外
   没有索引
2. 删除主键会失败
   如果表被架构绑定视图引用，则
3. 删除主键将失败
   如果该表被引用
   外键约束
4. 更改列的数据类型
   如果该列用于
   非聚集索引
5. 如果您有一个非常大的表，
   删除聚集主键
   会将表转换为堆。
   删除所需的时间
   聚集索引会建议
   进程失败（事实上，
   它仍在运行）
6. 大概你需要代码
   之后清空 StringCollection
   索引脚本已执行

Answer 2

尝试通过以下方式再次创建主键。

Index index = new Index(table, "PK_tableNameTable");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;

//You will have to store the names of columns before deleting the key.
index.IndexedColumns.Add(new IndexedColumn(index,"ID")); 

table.Indexes.Add(index);

代码片段来源

Answer 3

索引对象可能已经丢失了对表的引用。
您是否尝试过将索引添加回表对象？

table.Indexes.Add(pk);

Answer 4

不要删除并重新创建索引，而是尝试禁用它，然后在使用 .Disable 和 .Enable 方法完成后重新启用它。

Answer 5

您可以选择通过 SMO 运行 SQL 脚本吗？我开始运行所有通过 ServerConnection.ExecuteNonQuery 进行结构数据库修改的脚本。与通常的 SqlCommand 等堆栈相比，稳定性有了很大的提高（也不与 GO-s 相比:-）最终成为一个非常有用的融合。当然是非 MARS 连接。

Answer 6

我遇到了同样的问题，SMO 告诉我索引中使用的列不存在。我通过在外键上调用 Script() 之前调用 Table 对象上的 Discover() 方法解决了这个问题。这与 Table.Refresh 不同，Table.Refresh 不会读取表的所有元数据。调用 Table.Discover() 会明显减慢代码速度，但调用foreignkey.script 会成功。您不必保存 Discover 返回的列表，除非您需要它。但强制以这种方式获取元数据确实可以使脚本功能正常工作。