DROP DEFAULT (Transact-SQL)
Removes one or more user-defined defaults from the current database.
|DROP DEFAULT will be removed in a future version of Microsoft SQL Server. Avoid using DROP DEFAULT in new development work, and plan to modify applications that currently use them. Instead, use default definitions that you can create by using the DEFAULT keyword of ALTER TABLE or CREATE TABLE. For more information, see Creating and Modifying DEFAULT Definitions.|
Is the name of the schema to which the default belongs.
Is the name of an existing default. To see a list of defaults that exist, execute sp_help. Defaults must comply with the rules foridentifiers. Specifying the default schema name is optional.
Before dropping a default, unbind the default by executing sp_unbindefault if the default is currently bound to a column or an alias data type.
After a default is dropped from a column that allows for null values, NULL is inserted in that position when rows are added and no value is explicitly supplied. After a default is dropped from a NOT NULL column, an error message is returned when rows are added and no value is explicitly supplied. These rows are added later as part of the typical INSERT statement behavior.
A. Dropping a default
If a default has not been bound to a column or to an alias data type, it can just be dropped using DROP DEFAULT. The following example removes the user-created default named
USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = 'datedflt' AND type = 'D') DROP DEFAULT datedflt GO
B. Dropping a default that has been bound to a column
The following example unbinds the default associated with the
EmergencyContactPhone column of the
Employee table and then drops the default named
USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.objects WHERE name = 'phonedflt' AND type = 'D') BEGIN EXEC sp_unbindefault 'Person.Contact.Phone' DROP DEFAULT phonedflt END GO