sp_bindefault

Description

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

Syntax

sp_bindefault defname, objname [, futureonly]

Parameters

defname

is the name of a default created with create default statements to bind to specific columns or user-defined datatypes.

objname

is the name of the table and column, or user-defined datatype, to which the default is to be bound. If the objname parameter is not of the form “table.column”, it is assumed to be a user-defined datatype. If the object name includes embedded blanks or punctuation, or is a reserved word, enclose it in quotation marks.

Existing columns of the user-defined datatype inherit the default defname, unless you specify futureonly.

You cannot bind defaults to computed columns.

futureonly

prevents existing columns of a user-defined datatype from acquiring the new default. This parameter is optional when you are binding a default to a user-defined datatype. It is never used to bind a default to a column.

Examples

Example 1

Assuming that a default named today has been defined in the current database with create default, this command binds it to the startdate column of the employees table. Each new row added to the employees table has the value of the today default in the startdate column, unless another value is supplied:

sp_bindefault today, "employees.startdate"

Example 2

Assuming that a default named def_ssn and a user-defined datatype named ssn exist, this command binds def_ssn to ssn. 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’s default has previously been changed (in which case the changed default is maintained):

sp_bindefault def_ssn, ssn

Example 3

Binds the default def_ssn to the user-defined datatype ssn. Because the futureonly parameter is included, no existing columns of type ssn are affected:

sp_bindefault def_ssn, ssn, futureonly

Usage

Permissions

You must be the table owner or the user datatype owner to execute sp_bindefault. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

6

bind

sp_bindefault

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – Name of default

  • Proxy information – Original login name, if set proxy in effect

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands create default, create table, drop default

System procedures sp_unbindefault