sp_unbindefault (T-SQL)

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

Syntax

sp_unbindefault [@objname =] 'object_name'
    [, [@futureonly =] 'futureonly_flag']

Arguments

[@objname =] 'object_name'

Is the name of the table and column or the user-defined data type from which the default is to be unbound. object_name is nvarchar(776), with no default. If the parameter is not of the form table.column, object_name is assumed to be a user-defined data type. When unbinding a default from a user-defined data type, any columns of that data type that have the same default are also unbound. Columns of that data type with defaults bound directly to them are unaffected.

Note object_name can contain the [ and ] characters as delimited identifier characters. For more information, see Delimited Identifiers.

[@futureonly =] 'futureonly_flag'

Is used only when unbinding a default from a user-defined data type. futureonly_flag is varchar(15), with a default of NULL. When futureonly_flag is futureonly, existing columns of the data type do not lose the specified default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

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

When a default is bound to a column, the information about binding is removed from the syscolumns table. When a default is bound to a user-defined data type, the information is removed from the systypes table.

Permissions

Execute permissions default to the owner of the specified object.

Examples

A. Unbind a default from a column

This example unbinds the default from the hiredate column of an employees table.

EXEC sp_unbindefault 'employees.hiredate'

B. Unbind a default from a user-defined data type

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

EXEC sp_unbindefault 'ssn'

C. Use the futureonly_flag

This example unbinds future uses of the user-defined data type ssn without affecting existing ssn columns.

EXEC sp_unbindefault 'ssn', 'futureonly'

D. Use delimited identifiers

This example shows the use of delimited identifiers in object_name.

CREATE TABLE [t.3] (c1 int) -- Notice the period as part of the table

-- name.

CREATE DEFAULT default2 AS 0

GO

EXEC sp_bindefault 'default2', '[t.3].c1'

-- The object contains two periods;

-- the first is part of the table name and the second

-- distinguishes the table name from the column name.

EXEC sp_unbindefault '[t.3].c1'

See Also