DROP DEFAULT Statement

Removes a user-defined default from a database.

The DROP DEFAULT statement does not apply to DEFAULT constraints. For details about dropping DEFAULT constraints, see the ALTER TABLE statement.

Syntax

DROP DEFAULT [owner.]default_name [, [owner.]default_name...]

where

default_name

Specifies the name of an existing default. You can find out what defaults exist by executing the sp_help system stored procedure.

Remarks

To drop a default, you must first unbind it by using the sp_unbindefault system stored procedure if it is currently bound to a column or to a user-defined datatype.

When you drop a default from a column that allows null values, NULL is inserted in that position when rows are added and no value is explicitly supplied. When you drop a default from a NOT NULL column, you get an error message when rows are added and no value is explicitly supplied.

Permission

DROP DEFAULT permission defaults to the owner of the default and is not transferable. However, the database owner and system administrator can drop any object by specifying the owner in the DROP DEFAULT statement. The database owner can also use the SETUSER statement to impersonate another user.

Examples

A.    Drop a Default

If a default has not been bound to a column or to a user-defined datatype, it can simply be dropped using the DROP DEFAULT statement. This example removes the user-created default datedflt.

DROP DEFAULT datedflt

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 phonedflt default.

sp_unbindefault 'authors.phone'
go
DROP DEFAULT phonedflt

See Also