sp_unbindefault
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)
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
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.
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'