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