Binds a user-defined default to a column or user-defined datatype.
sp_bindefault defname, objname [, futureonly]
is the name of a default created with create default statements to bind to specific columns or user-defined datatypes.
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.
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.
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"
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
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
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 Adaptive 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, Adaptive 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.
Only the object owner can execute sp_bindefault.
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 |
|
38 |
exec_procedure |
Execution of a procedure |
|
Commands create default, create table, drop default
System procedures sp_unbindefault
