sp_bindefault System Stored Procedure

Binds a default to a column or to a user-defined datatype.

Syntax

sp_bindefault defname, objname [, futureonly]

where

defname: Is the name of the default created by the CREATE DEFAULT statement.
objname: Specifies the table and column or the user-defined datatype to which the default is to be bound. If objname is not of the form 'table.column', it is assumed to be a user-defined datatype. (Quotation marks are required around all system stored procedure parameters that have embedded blanks or punctuation.) By default, existing columns of the user-defined datatype inherit the default defname unless a default has been bound directly to the column. A default cannot be bound to a column of type timestamp or a column with the IDENTITY property.
futureonly: Used only when binding a default to a user-defined datatype, this option prevents existing columns of that datatype from inheriting the new default. It is never used when binding a default to a column. If futureonly is specified, any existing default bound to that datatype will be explicitly bound to any existing columns of that datatype that do not already have a default bound to them.

Remarks

First use the CREATE DEFAULT statement to create a default. Then execute the sp_bindefault system stored procedure to bind the default to a column or to a user-defined datatype in the current database. You can bind a new default to a column or to a user-defined datatype with sp_bindefault without unbinding an existing default. The old default is overridden. You cannot bind a default to a SQL Server - supplied datatype.

Existing columns of the user-defined datatype inherit the new default unless they have a default bound directly to them or unless the futureonly option is used. New columns of the user-defined datatype always inherit the default.

The default must be compatible with the datatype of the column. For example, you cannot use "N/A" as a default for a numeric column. If the default is not compatible with the column to which you've bound it, SQL Server returns an error message when it tries to insert the default value (not when you bind it). You cannot bind a default to a column that already has a DEFAULT constraint.

When you bind a default to a column, information is added to the syscolumns table. When you bind a default to a user-defined datatype, information is added to the systypes table.

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 startdate column of the employees table. Whenever a row is added to the employees table and data for the startdate column is not supplied, the column gets the value of the default today.

sp_bindefault today, 'employees.startdate'

B. Bind a Default to a User-defined Datatype

Assuming that a default named def_ssn and a user-defined datatype named ssn exist, this example binds the default def_ssn to the ssn user-defined datatype. The default is inherited by all columns that are assigned the user-defined datatype ssn when a table is created. Existing columns of type ssn also inherit the default def_ssn unless you specify futureonly (which prevents existing columns of that user-defined datatype from inheriting the default), or unless the column has a default bound directly to it. Defaults bound to columns always take precedence over those bound to datatypes.

sp_bindefault def_ssn, ssn

C. FUTUREONLY

This example binds the default def_ssn to the user-defined datatype ssn. Because futureonly is specified, no existing columns of type ssn are affected.

sp_bindefault def_ssn, ssn, FUTUREONLY

Permission

Execute permission defaults to the object owner.

Tables Used

syscolumns, sysobjects, systypes