sp_unbindefault

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

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, 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.

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

sp_unbindefault "employees.startdate"

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

sp_unbindefault ssn

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

sp_unbindefault ssn, futureonly

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.

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

Granular permissions enabled	With granular permissions enabled, you must be the object owner or the user datatype owner.
Granular permissions disabled	With granular permissions disabled, you must be the object owner.

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