sp_unbindefault System Stored Procedure

Unbinds (removes) a default from a column or from a user-defined datatype in the current database.

Syntax

sp_unbindefault objname [, futureonly]

where

objname

Specifies the table and column or the user-defined datatype from which the default is to be unbound. If the parameter is not of the form 'table.column', then objname is recognized as a user-defined datatype. When unbinding a default from a user-defined datatype, any columns of that datatype that have the same default as the user datatype are also unbound. Columns of that datatype with defaults bound directly to them are unaffected.

futureonly

Used only when unbinding a default from a user-defined datatype, this option prevents existing columns of that datatype from losing this default. It is never used when unbinding a default from a column.

Remarks

Columns of the user-defined datatype lose their current default unless their default has previously been changed or the futureonly option is used.

To display the text of a default, execute sp_helptext with the default name as the parameter.

When you unbind a default, the information about the binding is removed from the syscolumns table if the default was bound to a column, and from the systypes table if the default was bound to a user-defined datatype.

Examples

A.    Unbind a Default from a Column

This example unbinds the default from the startdate column of the employees table.

sp_unbindefault 'employees.startdate'

B.    Unbind a Default from a User-defined Datatype

This example unbinds the default from the user-defined datatype ssn and all columns of that type. It unbinds existing and future columns of that type.

sp_unbindefault ssn

C.    Use futureonly

This example unbinds future uses of the user datatype ssn, but existing columns of type ssn are unaffected.

sp_unbindefault ssn, FUTUREONLY

Permission

Execute permission defaults to the specified object owner.

Tables Used

syscolumns, sysobjects, systypes

See Also