A SQL Server RULE specifies the acceptable values that can be inserted into a database column. Let’s see how to find out if a RULE is bound to a column in SQL Server and how to remove them. A RULE is very much like a check constraint; except that they only work in T-SQL and Microsoft has deprecated them. A SQL Server RULE is intended to check information as new rows are inserted or when existing rows are updated. If the RULE is broken because the data is invalid the update fails. According to MSDN, if you’re thinking of using a SQL Server RULE… don’t. It’s deprecated.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE.

How to check if a SQL Server RULE is bound to a column

If you’re still reading it’s because you already have at least one SQL Server RULE in place. I’m sure you’ve seen tons of articles on how to create a SQL Server RULE, how to bind and unbind a SQL RULE rule on columns, and how to drop one. What I had trouble finding was information on how to check to see if a RULE is bound to a column or not.
SELECT so.name AS RuleNameI ran across this issue while tweaking an upgrade script. I typically use IF EXISTS very liberally before performing any kind of action in SQL. The code I was working on was attempting to unbind a SQL Server RULE from several tables, drop the RULE, then recreate the SQL Server RULE with the new values and rebind it to the tables. However, the rule was missing from one of the tables and this causing an error. You can run the statement below to query your data. Simply comment/uncomment the lines in the WHERE clause to search by table name, column name, or SQL Server RULE name.

	,OBJECT_NAME(sc.object_id) AS TableName
	,so.type_desc
	,sc.name AS ColumnName
FROM sys.objects so
JOIN sys.columns sc
	ON so.object_id = sc.rule_object_id
WHERE 
--sc.name = 'myCol' --search by column name
--AND OBJECT_NAME(sc.object_id) = 'myTable' --search by table name
so.name = 'chkRule' --search by rulename

Now that you have an idea for the data you’re looking at, using an EXISTS statement before trying to use sp_unbindRule is easy.

IF EXISTS (SELECT so.name 
	FROM sys.objects so
	JOIN sys.columns sc
		ON so.object_id = sc.rule_object_id
	WHERE 
		OBJECT_NAME(sc.object_id) = 'myTable' -- table name
		AND sc.name = 'myColumn' --column name)
BEGIN
	EXEC sp_unbindrule 'myTable.myColumn'
END