Skip to content

sp-unbindefault-transact-sql.md

Latest commit

 

History

History
112 lines (77 loc) · 4.27 KB

sp-unbindefault-transact-sql.md

File metadata and controls

112 lines (77 loc) · 4.27 KB
title sp_unbindefault (Transact-SQL)
description Unbinds, or removes, a default from a column or from an alias data type in the current database.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 04/08/2026
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_unbindefault_TSQL
sp_unbindefault
helpviewer_keywords
sp_unbindefault
dev_langs
TSQL

sp_unbindefault (Transact-SQL)

[!INCLUDE SQL Server]

Unbinds, or removes, a default from a column or from an alias data type in the current database.

Important

[!INCLUDE ssNoteDepFutureAvoid] Create default definitions by using the DEFAULT keyword in the ALTER TABLE or CREATE TABLE statements instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_unbindefault
    [ @objname = ] N'objname'
    [ , [ @futureonly = ] 'futureonly' ]
[ ; ]

Arguments

[ @objname = ] N'objname'

The name of the table and column, or the alias data type, to unbind the default from. @objname is nvarchar(776), with no default. [!INCLUDE ssNoVersion] attempts to resolve two-part identifiers to column names first, then to alias data types.

When unbinding a default from an alias data type, any columns of that data type that have the same default are also unbound. Columns of that data type with defaults bound directly to them are unaffected.

Note

@objname can contain brackets [] as delimited identifier characters. For more information, see Database identifiers.

[ @futureonly = ] 'futureonly'

Used only when unbinding a default from an alias data type. @futureonly is varchar(15), with a default of NULL. When @futureonly is futureonly, existing columns of the data type don't lose the specified default.

Return code values

0 (success) or 1 (failure).

Remarks

To display the text of a default, execute sp_helptext with the name of the default as the parameter.

Permissions

To unbind a default from a table column requires ALTER permission on the table. To unbind a default from an alias data type requires CONTROL permission on the type or ALTER permission on the schema to which the type belongs.

Examples

A. Unbind a default from a column

The following example unbinds the default from the hiredate column of an employees table.

EXECUTE sp_unbindefault 'employees.hiredate';

B. Unbind a default from an alias data type

The following example unbinds the default from the alias data type ssn. It unbinds existing and future columns of that type.

EXECUTE sp_unbindefault 'ssn';

C. Use the futureonly_flag

The following example unbinds future uses of the alias data type ssn without affecting existing ssn columns.

EXECUTE sp_unbindefault 'ssn', 'futureonly';

D. Use delimited identifiers

The following example shows using delimited identifiers in the @objname parameter. The table name t.3 contains a period, so bracket delimiters are required. In the sp_unbindefault call, the first period is part of the table name and the second separates the table name from the column name.

CREATE TABLE [t.3] (c1 INT);
CREATE DEFAULT default2 AS 0;
GO

EXECUTE sp_bindefault 'default2', '[t.3].c1';
EXECUTE sp_unbindefault '[t.3].c1';

Related content