DROP DEFAULT (T-SQL)

Removes one or more user-defined defaults from the current database.

The DROP DEFAULT statement does not apply to DEFAULT constraints. For information about dropping DEFAULT constraints (created by using the DEFAULT option of either the CREATE TABLE or ALTER TABLE statements), see ALTER TABLE.

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 permission defaults to the owner of the default, and is not transferable. However, members of the db_owner fixed database role or members of the sysadmin fixed database 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

See Also