DROP DEFAULT
Removes one or more user-defined defaults from the current database.
The DROP DEFAULT statement does not apply to DEFAULT constraints. For more information about dropping DEFAULT constraints (created by using the DEFAULT option of either the CREATE TABLE or ALTER TABLE statements), see "ALTER TABLE" in this volume.
Syntax
DROP DEFAULT { default } [ ,...n ]
Arguments
default
Is the name of an existing default. To see a list of defaults that exist, execute sp_help. Defaults must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the default owner name is optional.
n
Is a placeholder indicating that multiple defaults can be specified.
Remarks
Before dropping a default, unbind the default by executing sp_unbindefault (if the default is currently bound to a column or a user-defined data type).
After a default is dropped from a column that allows 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 normal INSERT statement behavior.
Permissions
DROP DEFAULT permissions default to the owner of the default, and are not transferable. However, members of the db_owner and db_ddladmin fixed database roles and the sysadmin fixed server role can drop any default object by specifying the owner in DROP DEFAULT.
Examples
A. Drop a default
If a default has not been bound to a column or to a user-defined data type, it can simply be dropped using DROP DEFAULT. This example removes the user-created default named datedflt.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'datedflt'
AND type = 'D')
DROP DEFAULT datedflt
GO
B. Drop a default that has been bound to a column
This example unbinds the default associated with the phone column of the authors table and then drops the default named phonedflt.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'phonedflt'
AND type = 'D')
BEGIN
EXEC sp_unbindefault 'authors.phone'
DROP DEFAULT phonedflt
END
GO