Unbinds a created default value from a column or from a user-defined datatype.
sp_unbindefault objname [, futureonly]
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.
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.
Only the object owner can execute sp_unbindefault.
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 |
|
67 |
unbind |
sp_unbindefault |
|
Commands create default, drop default
System procedures sp_bindefault, sp_helptext