Using the Transact-SQL DROP DEFAULT Statement to Remove a Default from a Database

When you no longer need a default you've created, you can use the Transact-SQL DROP DEFAULT statement to permanently remove the default from the database in which you created it. The syntax of the DROP DEFAULT statement is

DROP DEFAULT 
 {, , ... ,]

where the is the name you gave the default when you defined it. As you can see from the syntax of the statement, you can drop multiple defaults with a single DROP DEFAULT.

In Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," you used the Transact-SQL statements

 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"

to create two defaults: UD_VALUE_UNKNOWN and UD_APPLIED_FOR. If you no longer need these defaults, execute the Transact-SQL statement

 DROP DEFAULT ud_value_unknown, ud_applied_for

to remove them from the database.

One important thing to know is that you cannot drop a default that is currently bound to either columns or user-defined data types. You must first use either the Enterprise Manager or the stored procedure sp_unbindefault to unbind the default from all columns and user-defined data types. Once you've completely unbound the default, you can remove it from the database.

If you create and use defaults, it is essential that you keep an accurate list of them and (perhaps more importantly) the columns and user-defined data types to which they are bound. Unfortunately, the error message the DBMS returns if you try to drop a column with a bound default states only the error and does not identify the bindings that caused it. For example, if you execute the statement

 DROP DEFAULT ud_applied_for

and UD_APPLIED_FOR is bound to a column or data type, the statement will fail and the DBMS will display the error message:

 The default 'ud_applied_for' cannot be dropped because it
is bound to one or more column.

To successfully execute the DROP DEFAULT statement, you will need to run the stored procedure sp_unbindefault. However, sp_unbindefault requires that you supply both the table and the column name in order to unbind a default. Moreover, MS-SQL Server does not have a stored procedure that will list the columns to which a default is bound, so you will need to refer to your documentation.

If you don't have a list of bindings for the default you want to remove, or if you prefer to use a GUI tool, you can use the MS-SQL Server Enterprise Manager to unbind and drop a default. First, perform the first eight steps of the bind default procedure in Tip 54, "Using the MS-SQL Server Enterprise Manager to Bind a Default to a Data Type or Table Column." After you complete Step 8, the DBMS will display the Bind Default to Columns dialog box (refer to Figure 54.3, in Tip 54).

Once you have the Bind Default to Columns dialog box on your screen, perform the following steps to unbind the default:

1. To find a table with one or more bindings to the default you want to remove, click your mouse on the drop-down list box to the right of the Table field. Enterprise Manager will display the list of database tables in the drop-down list box.

2. If you know the name of the table you want, select it from the drop-down list. (If you don't know the table you want, you will have to select each of the tables in the database, one at a time.) The Enterprise Manager will display the table columns bound to your default in the Bound Columns list,

3. To unbind the default from a column, click your mouse on the column name in the Bound Columns list, and then click your mouse on the Remove button.

4. Repeat Step 3 until you've removed all of the column names from the Bound Columns list.

5. If your documentation lists other tables to which the default is bound (or if you don't have documentation and you have not made it through the list of database tables), click your mouse on the Apply button and then continue at Step 3 to select the next table with which you want to work.

6. When you are finished unbinding the default from table columns, click your mouse on the OK button. Enterprise Manager will return to the Default Properties dialog box (similar to that shown in Figure 54.2, in Tip 54).

7. Click your mouse on the OK button. Enterprise Manager will return to its application window with defaults displayed in the right pane, similar to that shown in Figure 54.1, in Tip 54.

Now that you've unbound the default, you can perform the following steps to delete (drop) the default:

1. To select the default you want to delete (drop), click your mouse on the name of the default in the list of defaults in the right pane of the Enterprise Manager's application window.

2. To delete the default, click your mouse on the Delete button (the red X) on the Standard Toolbar (or select the Action menu Delete option). Enterprise Manager will display the name, owner, and type of the default you selected in the Drop Objects dialog box.

3. Click your mouse on the Drop All button.

To exit Enterprise Manager, click on the application window's close button (the X in the upper-right corner), or select the Console menu Exit option.