1 Version 4.0 -- 1/15/89 sp_bindefault
______________________________________________________________________
NAME: sp_bindefault
FUNCTION:
Binds a default to a column or to a user-defined datatype.
SYNTAX:
sp_bindefault defname, objname [, futureonly]
EXAMPLES:
1) sp_bindefault today, "employees.startdate"
Assuming that a default named today has been defined in the
sp_bindefault Version 4.0 -- 1/15/89 2
______________________________________________________________________
current database with CREATE DEFAULT, this command binds it
to the startdate column of the employees table. Whenever a
row is added to the employees table and data for the start-
date column is not supplied, the column gets the value of the
default today.
2) sp_bindefault def_ssn, ssn
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 datatype ssn when a table is created. Existing columns
of type ssn also inherit the default def_ssn, unless you use
the optional third parameter, futureonly (which prevents
existing columns of that user datatype from inheriting the
default), or unless the column's default has previously been
changed (in which case the changed default is maintained).
3 Version 4.0 -- 1/15/89 sp_bindefault
______________________________________________________________________
3) sp_bindefault def_ssn, ssn, futureonly
Binds the default def_ssn to the user datatype ssn. Because
the futureonly parameter is included, no existing columns of
type ssn are affected.
PARAMETERS:
defname - is the name of a default. Defaults are created with
CREATE DEFAULT statements and then bound to specific columns
or user-defined datatypes with sp_bindefault.
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 datatype. (Quotation marks are required
around all stored procedure parameters that have embedded
blanks or punctuation.)
sp_bindefault Version 4.0 -- 1/15/89 4
______________________________________________________________________
By default, existing columns of the user datatype inherit the
default defname, unless the column's default has previously
been changed.
futureonly - prevents existing columns of a user datatype from
inheriting the new default. This parameter is optional when
binding a default to a user-defined datatype. It is never
used when binding a default to a column.
COMMENTS:
o First use the CREATE DEFAULT statement to create a default.
Then execute sp_bindefault to bind it to a column or user data-
type in the current database.
o You cannot bind a default to a SQL Server-supplied datatype.
o If binding to a column, the objname argument must be of the
form "table.column". Any other format is taken to be the name
of a user datatype.
5 Version 4.0 -- 1/15/89 sp_bindefault
______________________________________________________________________
o You can bind a new default to a column or user datatype using
sp_bindefault without unbinding an existing default. The old
default is overridden.
o Existing columns of the user-defined datatype inherit the new
default unless their default has previously been changed, or
the value of the optional third parameter is futureonly. New
columns of the user-defined datatype always inherit the
default.
MESSAGES:
The default was successfully bound to the specified column in the
specified table.
The default was successfully bound to the specified user-defined
datatype.
The objname parameter supplied with the procedure contained a
reference to another database. Defaults can be bound to
sp_bindefault Version 4.0 -- 1/15/89 6
______________________________________________________________________
objects in the current database only.
First create the default in the current database with CREATE
DEFAULT. Then execute sp_bindefault.
Only the owner of a table can bind a default to any of its
columns. You are not the owner, or the object doesn't exist.
Only the owner of a user-defined datatype can bind a default to
it. You are not the owner.
Existing columns of the user-defined datatype specified now have
the new default bound to them (unless their defaults were
previously changed).
PERMISSIONS:
Execute permission to object owner.
TABLES USED:
syscolumns, sysobjects, systypes
7 Version 4.0 -- 1/15/89 sp_bindefault
______________________________________________________________________
SEE ALSO:
sp_bindrule, sp_unbindefault, sp_unbindrule, CREATE DEFAULT, DROP
DEFAULT