Export (0) Print
Expand All
Expand Minimize

DROP RULE (Transact-SQL)

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

ms186791.note(en-US,SQL.90).gifImportant:
DROP RULE will be removed in a future version of Microsoft SQL Server. Avoid using DROP RULE in new development work, and plan to modify applications that currently use them. Instead, use CHECK constraints that you can create by using the CHECK keyword of CREATE TABLE or ALTER TABLE. For more information, see CHECK Constraints

Topic link icon Transact-SQL Syntax Conventions


DROP RULE { [ schema_name . ] rule_name } [ ,...n ] [ ; ]

schema_name

Is the name of the schema to which the rule belongs.

rule

Is the rule to be removed. Rule names must comply with the rules for identifiers. For more information about rules for identifiers, see Using Identifiers As Object Names. Specifying the rule schema name is optional.

To drop a rule, first unbind it if the rule is currently bound to a column or to an alias data type. To unbind the rule, use sp_unbindrule. If the rule is bound when you try to drop it, an error message is displayed and the DROP RULE statement is canceled.

After a rule is dropped, new data entered into the columns previously governed by the rule is entered without the constraints of the rule. Existing data is not affected in any way.

The DROP RULE statement does not apply to CHECK constraints. For more information about dropping CHECK constraints, see ALTER TABLE (Transact-SQL).

To execute DROP RULE, at a minimum, a user must have ALTER permission on the schema to which the rule belongs.

The following example unbinds and then drops the rule named VendorID_rule.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'VendorID_rule'
            AND type = 'R')
   BEGIN
      EXEC sp_unbindrule 'Production.ProductVendor.VendorID'
      DROP RULE VendorID_rule
   END
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft