sp_bindefault
Binds a default to a column or to a user-defined data type.
Syntax
sp_bindefault [ @defname = ] 'default' ,
[ @objname = ] 'object_name'
[ , [ @futureonly = ] 'futureonly_flag' ]
Arguments
[@defname =] 'default'
Is the name of the default created by the CREATE DEFAULT statement. default is nvarchar(776), with no default.
[@objname =] 'object_name'
Is the name of table and column or the user-defined data type to which the default is to be bound. object_name is nvarchar(517), with no default. If object_name is not of the form table.column, it is assumed to be a user-defined data type. By default, existing columns of the user-defined data type inherit default unless a default has been bound directly to the column. A default cannot be bound to a column of timestamp data type, a column with the IDENTITY property, or a column that already has a DEFAULT constraint.
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 binding a default to a user-defined data type. futureonly_flag is varchar(15), with a default of NULL. This parameter when set to futureonly prevents existing columns of that data type from inheriting the new default. It is never used when binding a default to a column. If futureonly_flag is NULL, the new default is bound to any columns of the user-defined data type that currently have no default or that are using the existing default of the user-defined data type.
Return Code Values
0 (success) or 1 (failure)
Remarks
You can bind a new default to a column (although using the DEFAULT constraint is preferred) or to a user-defined data type with sp_bindefault without unbinding an existing default. The old default is overridden. You cannot bind a default to a Microsoft® SQL Server™ data type. If the default is not compatible with the column to which you have bound it, SQL Server returns an error message when it tries to insert the default value (not when you bind it).
Existing columns of the user-defined data type inherit the new default unless they have a default bound directly to them or unless futureonly_flag is specified as futureonly. New columns of the user-defined data type always inherit the default.
When you bind a default to a column, related information is added to the syscolumns table. When you bind a default to a user-defined data type, related information is added to 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_bindefault.
Examples
A. Bind a default to a column
Assuming that a default named today has been defined in the current database by the CREATE DEFAULT statement, this example binds the default to the hire date column of the employees table. Whenever a row is added to the employees table and data for the hire date column is not supplied, the column gets the value of the default today.
USE master
EXEC sp_bindefault 'today', 'employees.[hire date]'
B. Bind a default to a user-defined data type
Assuming that a default named def_ssn and a user-defined data type named ssn exist, this example binds the default def_ssn to the ssn user-defined data type. The default is inherited by all columns that are assigned the user-defined data type ssn when a table is created. Existing columns of type ssn also inherit the default def_ssn unless futureonly is specified for futureonly_flag value, or unless the column has a default bound directly to it. Defaults bound to columns always take precedence over those bound to data types.
USE master
EXEC sp_bindefault 'def_ssn', 'ssn'
C. Use the futureonly_flag
This example binds the default def_ssn to the user-defined data type ssn. Because futureonly is specified, no existing columns of type ssn are affected.
USE master
EXEC sp_bindefault 'def_ssn', 'ssn', 'futureonly'
D. Use delimited identifiers
This example shows the use of delimited identifiers in object_name.
USE master
CREATE TABLE [t.1] (c1 int)
-- Notice the period as part of the table name.
EXEC sp_bindefault 'default1', '[t.1].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.