How to drop and recreate and primary key index using SMO with SQL Server?

Question

I am using SQL Server 2005 Express. I want to use SMO to loop through each table in a database and change each Char column to a Varchar column. If a column is a member of the primary key, I need to first drop the primary key before altering the datatype of the column. Then I need to recreate the index. Here is the code I am trying to use:

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();
    }
}

But when I try to create the index I get an exception with the message "Cannot access properties or methods for the Microsoft.SqlServer.Management.Smo.Index '[PK_table1]', because it has been dropped." So is there a good way of accomplishing what I want to do with SMO?

I tried scripting the index before I dropped it using the Script method of Index, but it throws an exception with the message "The Index 'PK_table1' references nonexistent column '[table1].[owner]'." The owner column clearly exists.

Answer 1

Possibly the Index object has lost it's reference to the Table. Have you tried to Add the Index back to the table object ?

table.Indexes.Add(pk);

Answer 2

Instead of dropping and recreating the index, try just disabling it and then re-enabling it when you are done using the .Disable and .Enable methods.

Answer 3

Try creating primary key again in the following manner.

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);

Source of the snippet

Answer 4

I was able to drop the primary key, alter the column data types, and re-create the primary key with this code:

// 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);
    }                
} 

Some caveats:

1. The line that defines pk will throw an exception if there is a table without indexes
2. Dropping the primary key will fail if the table is referenced by a schema-bound view
3. Dropping the primary key will fail if the table is referenced by foreign key constraints
4. Changing the data type of the column will fail if that column is used in a nonclustered index
5. If you have a very large table, dropping a clustered primary key will convert the table to a heap. The time taken to to remove the clustered index will suggest the process has failed (while, in fact, it is still running)
6. Presumably you would need code to empty the StringCollection after the index script was executed

Answer 5

Do you have an option to run SQL scripts through SMO? I started running all scripts that do any structural DB modifications via ServerConnection.ExecuteNonQuery. Big stability improvement over usual SqlCommand etc. stack (doesn't compain against GO-s either :-) Ended up being a very usefull fusion. Non-MARS connection of course.