sp_unbindefault

Syntax

sp_unbindefault objname [, futureonly]

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, with a default that 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.

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
```

There are additional considerations when using sp_unbindefault:

Use sp_unbindefault to remove defaults created with sp_bindefault. Use alter table to drop defaults declared using the create table or alter table statements.
Columns of a user-defined datatype lose their current default unless the default has been changed or the value of the optional second parameter is futureonly.
To display the text of a default, execute sp_helptext with the default name as the parameter.

See also create default, drop default in Reference Manual: Commands.

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

Setting	Description
Enabled	With granular permissions enabled, you must be the object owner or the user datatype owner.
Disabled	With granular permissions disabled, you must be the object owner.

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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

Information	Values
Event	67
Audit option	unbind
Command or access audited	sp_unbindefault
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 in effect