public override string Generate(AlterDefaultConstraintExpression expression)
{
const 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('{3}.{0}')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('{3}.{0}')
AND name = '{4}'
);
-- create alter table command to drop contraint as string and run it
SET @sql = N'ALTER TABLE {3}.{0} DROP CONSTRAINT ' + @default;
EXEC sp_executesql @sql;
-- create alter table command to create new default constraint as string and run it
ALTER TABLE {3}.{0} WITH NOCHECK ADD CONSTRAINT {5} DEFAULT({2}) FOR {1};";
return(String.Format(sql,
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(expression.ColumnName),
Quoter.QuoteValue(expression.DefaultValue),
Quoter.QuoteSchemaName(expression.SchemaName),
expression.ColumnName,
SqlServerColumn.GetDefaultConstraintName(expression.TableName, expression.ColumnName)));
}
public override string Generate(AlterDefaultConstraintExpression expression)
{
// before we alter a default constraint on a column, we have to drop any default value constraints in SQL Server
var builder = new StringBuilder();
builder.AppendLine(Generate(new DeleteDefaultConstraintExpression
{
ColumnName = expression.ColumnName,
SchemaName = expression.SchemaName,
TableName = expression.TableName
}));
builder.AppendLine();
builder.Append(String.Format("-- create alter table command to create new default constraint as string and run it" + Environment.NewLine + "ALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT {3} DEFAULT({2}) FOR {1};",
Quoter.QuoteTableName(expression.TableName),
Quoter.QuoteColumnName(expression.ColumnName),
Quoter.QuoteValue(expression.DefaultValue),
Quoter.QuoteConstraintName(SqlServerColumn.GetDefaultConstraintName(expression.TableName, expression.ColumnName))));
return(builder.ToString());
}
