Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

DROP DEFAULT

SQL Server 2000

Removes one or more user-defined defaults from the current database.

The DROP DEFAULT statement does not apply to DEFAULT constraints. For more information about dropping DEFAULT constraints (created by using the DEFAULT option of either the CREATE TABLE or ALTER TABLE statements), see "ALTER TABLE" in this volume.

Syntax

DROP DEFAULT { default } [ ,...n ]

Arguments

default

Is the name of an existing default. To see a list of defaults that exist, execute sp_help. Defaults must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the default owner name is optional.

n

Is a placeholder indicating that multiple defaults can be specified.

Remarks

Before dropping a default, unbind the default by executing sp_unbindefault (if the default is currently bound to a column or a user-defined data type).

After a default is dropped from a column that allows null values, NULL is inserted in that position when rows are added and no value is explicitly supplied. After a default is dropped from a NOT NULL column, an error message is returned when rows are added and no value is explicitly supplied. These rows are added later as part of the normal INSERT statement behavior.

Permissions

DROP DEFAULT permissions default to the owner of the default, and are not transferable. However, members of the db_owner and db_ddladmin fixed database roles and the sysadmin fixed server role can drop any default object by specifying the owner in DROP DEFAULT.

Examples
A. Drop a default

If a default has not been bound to a column or to a user-defined data type, it can simply be dropped using DROP DEFAULT. This example removes the user-created default named datedflt.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'datedflt' 
            AND type = 'D')
   DROP DEFAULT datedflt
GO
B. Drop a default that has been bound to a column

This example unbinds the default associated with the phone column of the authors table and then drops the default named phonedflt.

USE pubs
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'phonedflt' 
            AND type = 'D')
   BEGIN 
      EXEC sp_unbindefault 'authors.phone'
      DROP DEFAULT phonedflt
   END
GO

See Also

CREATE DEFAULT

sp_helptext

sp_help

sp_unbindefault

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft