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.

Duckling

If a column exists and SQL says it doesn't, I'd be looking at security issues - what context are your commands running under? – Illuminative

The commands are running under the system administrator login (sa). – Duckling

It is more complicated that you know. You cannot simply drop the primary key if it is referenced in a foreign key relationship. Thus, for each PK, you would need to find all FK references, drop those, drop the PK, change the data type, and then add them back in reverse order. – With

@Thomas. That is true. There are no foreign keys in this database though. – Duckling

Answer 1

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

Eskimo

Again when I run the Script method I get a FailedOperationException saying that "The Index 'PK_table1' references nonexistent column '[dbo].[table1].[owner]'". The column obviously exists. I can go into SQL Server Management Studio, script the index from there, look at the script and clearly see that the [owner] column is in there. I can use Management Studio to create a new database, a new table, then try to script the primary key on that using SMO and I get the very same FailedOperationException. All using the sa login. – Duckling

Can you post the schema of the table? I was able to get this running on a small db with 2 simple tables. – Eskimo

Another thought: have you tried running Profiler against the database while running the code? This will show the exact t-SQL statements executed from the SMO. – Eskimo

I realize now I left out that I am using the Express edition of SQL Server 2005. Unfortunately it does not come with the Profiler. – Duckling

Same here. It's a little more work but you can do the same thing with a server side trace (profiler is essentially a GUI): tinyurl.com/p6orqs – Eskimo

What version if SMO does this work on? I am using 11 I think (SQL Server 2012 SDK) and the Indexes collection is not enumerable. The Cast nor SingleOrDefault work. – Goldman

Answer 2

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

Contrapuntal

This is what I was thinking I would have to end up doing. I was trying to avoid though having to replicate all the various properties of the index. What would be nice is a kind of clone feature. – Duckling

This might be handy for profiling on sqlexpress. #47876 – Contrapuntal

Answer 3

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

Foiled

Unfortunately this does not work. I tried the above statement before "pk.Create()" and get the same exception. I tried it and then "table.Alter()" instead of Create and get the same exception. – Duckling

Answer 4

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.

Thracian

This does not work. After disabling the index, altering the columns and calling "table.Alter()", I get an exception with the message "Cannot perform the specified operation on table 'table1' because its clustered index 'PK_table1' is disabled." – Duckling

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.

Lobeline

Answer 6

I ran into the same issue with SMO telling me that columns used in the index didn't exist. I solved it by calling the Discover() method on the Table object prior to calling Script() on my ForeignKey. This is different from Table.Refresh which doesn't read all of the table's metadata. Calling Table.Discover() noticably slows down the code but the call to ForeignKey.Script succeeds after doing so. You don't have to save the List that Discover returns unless you need it for something. But forcing the metadata to be fetched in this way does make the scripting function work.

Mourner