sp_unbindefault (Transact-SQL)

Unbinds, or removes, a default from a column or from an alias data type in the current database.


This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you create default definitions by using the DEFAULT keyword in the ALTER TABLE or CREATE TABLE statements instead. For more information, see Creating and Modifying DEFAULT Definitions.

Topic link iconTransact-SQL Syntax Conventions


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


  • [ @objname= ] 'object_name'
    Is the name of the table and column or the alias data type from which the default is to be unbound. object_name is nvarchar(776), with no default. SQL Server attempts to resolve two-part identifiers to column names first, then to alias data types.

    When unbinding a default from an alias 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.


    object_name can contain brackets [] as delimited identifier characters. For more information, see Delimited Identifiers (Database Engine).

  • [ @futureonly= ] 'futureonly_flag'
    Is used only when unbinding a default from an alias 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.


To unbind a default from a table column requires ALTER permission on the table. To unbind a default from an alias data type requires CONTROL permission on the type or ALTER permission on the schema to which the type belongs.


A. Unbinding a default from a column

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

EXEC sp_unbindefault 'employees.hiredate'

B. Unbinding a default from an alias data type

The following example unbinds the default from the alias data type ssn. It unbinds existing and future columns of that type.

EXEC sp_unbindefault 'ssn'

C. Using the futureonly_flag

The following example unbinds future uses of the alias data type ssn without affecting existing ssn columns.

EXEC sp_unbindefault 'ssn', 'futureonly'

D. Using delimited identifiers

The following example shows using delimited identifiers in object_name parameter.

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 (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
sp_bindefault (Transact-SQL)
sp_helptext (Transact-SQL)
System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance