How To Check If A Rule Is Bound To A Column In SQL Server 2000 And SQL Server 2005

All of us would be aware of RULES in SQL Server. A RULE is very similar to a check constraint; however there are few differences between them.

Most of the articles in web would have covered how to create a rule, bind a rule to table columns, and unbind the rule and dropping it. But as far as I have seen there are not many articles that speak about how to check if a rule is  bound to a table column or just exist. This article is intended to cover that part.

Before covering the main theme of this article, I wanted to put forth few points which I feel is  important for us to understand

1. When we create a rule , It would not be bound to a table column, we have to explicitly bind that rule to the column
2. When we unbind a rule from a table column, the rule is unbound from the column, but the object is not dropped. It exists in the database.
3. Before dropping a rule, it should be unbound from the column.

Now, let’s get inside how we can check if a rule is bound to a table column or not.First I am creating a table "TestRule" having id, Name and Age column

create table TestRule
(
    ID int identity(1,1),
    Name varchar(50),
    Age int
)

Secondly I am creating a rule with the name "NewRule" which allows age between 0 and 90.

create rule dbo.NewRule 
as 
@Age > 0 and @Age < 90

Finally I want to bind this rule to the age column in my table

sp_bindrule 'NewRule','TestRule.Age'

Yes, we have successfully bound the rule to the column.

To check if the rule is successfully bound or not, we have to query the sys.columns table in SQL Server 2005 and syscolumns in SQL Server 2000.In SQL Server 2005 , the sys.columns table has a column called "rule_object_id" which says if the rule is bound to a column or not. So our query would look like this

select rule_object_id from sys.columns where name like 'Age' and object_id = object_id('TestRule')

Similarly in SQL Server 2000 , the syscolumns table has a column called "Domain" which says if the column is bound with a rule or not. To check this, run the given query

select domain from syscolumns where name = 'Age' and id = object_id('TestRule')

Irrespective of the columns that we are checking in sys.columns and syscolumns, if the values in those columns are greater than 0, then it says that the rule is bound to the column. Otherwise it isn’t.. The output of both the queries would be the same. If you unbind the rules from the column and run the same query, the values would be zero. To unbind a rule from a column, run the following query

sp_unbindrule 'TestRule.Age'

Checking if a rule is bound to a column is not very straight forward as how we check if a column exists or not. I hope this article would be useful to you.