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.
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: