Deleting a column with a default value throws an error in SQL Server

[shawng]

Deleting a column with a default value throws an error in SQL Server, since SQL Server creates a default constraint to enforce the default value. If you are dropping a column, you don't need the default value constrain anymore either, so it should be safe to delete. Here's some code to do it (replace line 71 of SqlServerGenerator.cs with this, if it isn't horribly mangled by the editor):

public override string Generate(DeleteColumnExpression expression)
{
// before we drop a column, we have to drop any default value constraints in SQL Server
string sql = @"
DECLARE @default sysname, @SQL nvarchar(max);

    -- get name of default constraint
    SELECT @default = name 
    FROM sys.default_constraints 
    WHERE parent_object_id = object_id('{0}')
    AND type = 'D'
    AND parent_column_id = (
        SELECT column_id 
        FROM sys.columns 
        WHERE object_id = object_id('{0}')
        AND name = '{1}'
    );

    -- create alter table command as string and run it
    SET @sql = N'ALTER TABLE [{0}] DROP CONSTRAINT ' + @default;
    EXEC sp_executesql @sql;

    -- now we can finally drop column
    ALTER TABLE [{0}] DROP COLUMN [{1}];";

return FormatExpression(sql, expression.TableName, expression.ColumnName);

}