drop default

Removes a user-defined default.

Syntax

drop default [owner.]default_name 
	[, [owner.]default_name] ...

Parameters

Examples

Usage

  • You cannot drop a default that is currently bound to a column or to a user-defined datatype. Use sp_unbindefault to unbind the default before you drop it.

  • You can bind a new default to a column or user-defined datatype without unbinding its current default. The new default overrides the old one.

  • When you drop a default for a NULL column, NULL becomes the column’s default value. When you drop a default for a NOT NULL column, an error message appears if users do not explicitly enter a value for that column when inserting data.

See also sp_help, sp_helptext, sp_unbindefault in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for drop default differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be the default owner or a user with drop any default privilege.

Disabled

With granular permissions disabled, you must be the default owner or a user with sa_role.

drop default permission defaults to the owner of the default and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

31

Audit option

drop

Command or access audited

drop default

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
create default