sp_unbindefault

Description

Unbinds a created default value from a column or from a user-defined datatype.

Syntax

sp_unbindefault objname [, futureonly]

Parameters

objname

is the name of either the table and column or the user-defined datatype from which to unbind the default. If the parameter is not of the form “table.column”, then objname is assumed to be a user-defined datatype. When unbinding a default from a user-defined datatype, any columns of that type that have the same default as the user-defined datatype are also unbound. Columns of that type, whose default has already been changed, are unaffected.

futureonly

prevents existing columns of the specified user-defined datatype from losing their defaults. It is ignored when unbinding a default from a column.

Examples

Example 1

Unbinds the default from the startdate column of the employees table:

sp_unbindefault "employees.startdate"

Example 2

Unbinds the default from the user-defined datatype named ssn and all columns of that type:

sp_unbindefault ssn

Example 3

Unbinds defaults from the user-defined datatype ssn, but does not affect existing columns of that type:

sp_unbindefault ssn, futureonly

Usage

Permissions

Only the object owner can execute sp_unbindefault.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

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

67

unbind

sp_unbindefault

  • 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 in effect

See also

Commands create default, drop default

System procedures sp_bindefault, sp_helptext