| title | sp_unbindrule (Transact-SQL) | ||
|---|---|---|---|
| description | Unbinds a rule from a column or an alias data type in the current database. | ||
| author | markingmyname | ||
| ms.author | maghan | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE SQL Server]
Unbinds a rule from a column or an alias data type in the current database.
Important
[!INCLUDE ssNoteDepFutureAvoid] We recommend that you 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
sp_unbindrule
[ @objname = ] N'objname'
[ , [ @futureonly = ] 'futureonly' ]
[ ; ]
The name of the table and column or the alias data type from which the rule is unbound. @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 rule from an alias data type, any columns of the data type that have the same rule are also unbound. Columns of that data type with rules bound directly to them are unaffected.
Note
@objname can contain brackets [] as delimited identifier characters. For more information, see Database identifiers.
Used only when unbinding a rule from an alias data type. @futureonly is varchar(15), with a default of NULL. When @futureonly is futureonly, existing columns of that data type don't lose the specified rule.
0 (success) or 1 (failure).
To display the text of a rule, execute sp_helptext with the rule name as the parameter.
When a rule is unbound, the information about the binding is removed from the sys.columns table if the rule was bound to a column, and from the sys.types table if the rule was bound to an alias data type.
When a rule is unbound from an alias data type, it's also unbound from any columns having that alias data type. The rule might also still be bound to columns whose data types were later changed by the ALTER COLUMN clause of an ALTER TABLE statement, you must specifically unbind the rule from these columns by using sp_unbindrule and specifying the column name.
To unbind a rule from a table column requires ALTER permission on the table. To unbind a rule from an alias data type requires CONTROL permission on the type or ALTER permission on the schema to which the type belongs.
The following example unbinds the rule from the startdate column of an employees table.
EXECUTE sp_unbindrule 'employees.startdate';The following example unbinds the rule from the alias data type ssn. It unbinds the rule from existing and future columns of that type.
EXECUTE sp_unbindrule ssn;The following example unbinds the rule from the alias data type ssn without affecting existing ssn columns.
EXECUTE sp_unbindrule 'ssn', 'futureonly';The following example shows using delimited identifiers in the @objname parameter. Notice the period as part of the table name. In the sp_bindrule portion, the object contains two periods; the first is part of the table name, and the second distinguishes the table name from the column name.
CREATE TABLE [t.4] (c1 INT);
GO
CREATE RULE rule2 AS @value > 100;
GO
EXECUTE sp_bindrule rule2, '[t.4].c1';
GO
EXECUTE sp_unbindrule '[t.4].c1';