sp_unbindrule (T-SQL)

Unbinds a rule from a column or a user-defined data type in the current database.

Syntax

sp_unbindrule [@objname =] 'object_name'
    [, [@futureonly =] 'futureonly_flag']

Arguments

[@objname =] 'object_name'

Is the name of the table and column or the user-defined data type from which the rule is unbound. object_name is nvarchar(776), with no default. If the parameter is not of the form table.column, object_name is assumed to be a user-defined data type. When unbinding a rule from a user-defined 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 object_name can contain the [ and ] characters as delimited identifier characters. For more information, see Delimited Identifiers.

[@futureonly =] 'futureonly_flag'

Is used only when unbinding a rule from a user-defined data type. futureonly_flag is varchar(15), with a default of NULL. When futureonly_flag is futureonly, existing columns of that data type do not lose the specified rule.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

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 syscolumns table if the rule was bound to a column, and from the systypes table if the rule was bound to a user-defined data type.

When a rule is unbound from a user-defined data type, it is also unbound from any columns having that user-defined data type. The rule may 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 using sp_unbindrule and specifying the column name.

Permissions

Execute permissions default to the object owner.

Examples

A. Unbind a rule from a column

This example unbinds the rule from the startdate column of an employees table.

EXEC sp_unbindrule 'employees.startdate'

B. Unbind a rule from a user-defined data type

This example unbinds the rule from the user-defined data type ssn. It unbinds the rule from existing and future columns of that type.

EXEC sp_unbindrule ssn

C. Use futureonly_flag

This example unbinds the rule from the user-defined data type ssn without affecting existing ssn columns.

EXEC sp_unbindrule 'ssn', 'futureonly'

D. Use delimited identifiers

This example shows the use of delimited identifiers in the object_name.

CREATE TABLE [t.4] (c1 int) -- Notice the period as part of the table

-- name.

GO

CREATE RULE rule2 AS @value > 100

GO

EXEC sp_bindrule rule2, '[t.4].c1' -- The object contains two

-- periods; the first is part of the table name and the second

-- distinguishes the table name from the column name.

GO

EXEC sp_unbindrule '[t.4].c1'

See Also