sp_bindefault

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

There are additional considerations when using sp_bindefault:

You can create column defaults in two ways: by declaring the default as a column constraint in the create table or alter table statement or by creating the default using the create default statement and binding it to a column using sp_bindefault. Using create default, you can bind that default to more than one column in the database.
You cannot bind a default to an SAP ASE server-supplied datatype.
You cannot bind a default to a system table.
Defaults bound to a column or user-defined datatype with the IDENTITY property have no effect on column values. Each time you insert a row into the table, the SAP ASE server assigns the next sequential number to the IDENTITY column.
If binding a default to a column, give the objname argument in the form “table.column”. Any other format is assumed to be the name of a user-defined datatype.
If a default already exists on a column, you must remove it before binding a new default. Use sp_unbindefault to remove defaults created with sp_bindefault. To remove defaults created with create table or alter table, use alter table to replace the default with NULL.
Existing columns of the user-defined datatype inherit the new default unless you specify futureonly. New columns of the user-defined datatype always inherit the default. Binding a default to a user-defined datatype overrides defaults bound to columns of that type; to restore column bindings, unbind and rebind the column default.
Statements that use a default cannot be in the same batch as their sp_bindefault statement.

See also create default, create table, drop default in Reference Manual: Commands.

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:

Information	Values
Event	6
Audit option	bind
Command or access audited	sp_bindefault
Information in `extrainfo`	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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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