In SQL Server, we can drop DEFAULT constraints by using the ALTER TABLE statement with the DROP CONSTRAINT argument.

Example

Here’s a quick example to demonstrate:

ALTER TABLE Dogs 
DROP CONSTRAINT DF__Dogs__DogId__6C190EBB;

Result:

Commands completed successfully.

In this case, we had a table called Dogs with a DEFAULT constraint called DF__Dogs__DogId__6C190EBB. We altered the table by dropping the constraint.

About the DROP DEFAULT Statement

Although T-SQL currently includes a DROP DEFAULT statement that is used to drop a DEFAULT constraint, it will be removed from the next version of SQL Server as of the time of writing. Therefore, it’s recommended that you avoid using the DROP DEFAULT statement in any new development work and opt for the ALTER TABLE method above.

Even as I write this, trying to use DROP DEFAULT on my machine results in an error telling me to use ALTER TABLE instead:

DROP DEFAULT IF EXISTS DF__Dogs__DogId__6FE99F9F;

Result:

Msg 3717, Level 16, State 1, Line 1
Cannot drop a default constraint by DROP DEFAULT statement. Use ALTER TABLE to drop a constraint default.