Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize


SQL Server 2000

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


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


[@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)


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.


Only members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, and the table owner can execute sp_unbindefault.

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





System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft