drop-rule-transact-sql.md

title	DROP RULE (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	05/11/2017
ms.prod	sql
ms.prod_service	sql-database
ms.reviewer
ms.suite	sql
ms.technology	t-sql
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	DROP_RULE_TSQL DROP RULE
dev_langs	TSQL
helpviewer_keywords	rules [SQL Server], removing deleting roles DROP RULE statement removing roles dropping roles
ms.assetid	8370b730-7fd5-43fe-a7f6-8300b3caa16d
caps.latest.revision	33
author	CarlRabeler
ms.author	carlrab
manager	craigg

DROP RULE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

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

Important

DROP RULE will be removed in the next version of [!INCLUDEmsCoName][!INCLUDEssNoVersion]. Do not use 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 Unique Constraints and Check Constraints.

Transact-SQL Syntax Conventions

Syntax

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

Arguments

IF EXISTS
Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssSQL15] through current version).

Conditionally drops the rule only if it already exists.

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. Specifying the rule schema name is optional.

Remarks

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).

Permissions

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

Examples

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

sp_unbindrule 'Production.ProductVendor.VendorID'  
DROP RULE VendorID_rule  
GO  

See Also

CREATE RULE (Transact-SQL)
sp_bindrule (Transact-SQL)
sp_help (Transact-SQL)
sp_helptext (Transact-SQL)
sp_unbindrule (Transact-SQL)
USE (Transact-SQL)