Introduction

This article will help you identify, understand, and script out database rules that, for the most part, remain hidden unless you know where to look. Finding rules that are bound to columns is not easy an easy task. If you currently have these rules in place you should know rules are being removed in future versions of SQL Server according to Books On-Line:

The following is the Books On-Line definition of a column rule:

To prepare the development efforts needed to remove these "hidden" rules it is important to first identify them, list them out, and understand what you need to do to replace them. After some searching, it appears rules bound to data columns using sp_bindrule are not easy items to find. I know what you are thinking: "Scott, I can easily find the rules even in Management Studio listed under Programming\Rules for the database I care about." I understand and know that, but this is only half the battle. Finding where and what they are bound to is the other half of the story.

My Approach

I knew I had rules on some tables and columns in an existing database, so the logical thing was to script out the table in Management Studio. I should see the rules as part of the create table script - Right? Wrong. It turns out I was only able to find the rules by writing a query against the system views. Let me show you how. First create a sample table:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytest]') AND type in (N'U'))
DROP TABLE dbo.mytest
GO
CREATE TABLE dbo.mytest 
( col1 varchar(10)
 ,col2 varchar(2)
)
GO 

- Add a rule called GE_Zero_Val that will not allow a column value to go less than zero.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GE_Zero_Val]') AND OBJECTPROPERTY(object_id, N'IsRule') = 1)
DROP RULE [dbo].[GE_Zero_Val]
GO
CREATE RULE [dbo].[GE_Zero_Val]
 AS @col >= 0
GO 

- Add another rule:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GE_TooHigh_Val]') AND OBJECTPROPERTY(object_id, N'IsRule') = 1)
DROP RULE [dbo].[GE_TooHigh_Val]
GO
CREATE RULE [dbo].[GE_TooHigh_Val]
 AS @col >= 5
GO 

- Now bind our rules to the col1 and col2 in our mytest table:

sp_bindrule 'GE_Zero_Val','mytest.col1'
GO 

sp_bindrule 'GE_TooHigh_Val','mytest.col2'
GO 

Now go and find what those rules are bound to - I dare ya...This is where the fun part begins.

Now run the next script. It will show our rules, columns and the table they are bound to in this database.

SELECT 
o.name AS TableName
,c.name AS ColumnName
,(SELECT name FROM sys.objects WHERE object_id=c.rule_object_id) AS RuleName
FROM sys.columns AS c
INNER JOIN sys.objects AS o
ON c.object_id=o.object_id
WHERE o.is_ms_shipped <> 1
AND rule_object_id <> 0 

Now let's do something useful with this. I want to produce an output that will allow me to easily drop these nasty things on the next deployment cycle. Now because the statements must be separated with a 'GO', we will use a cursor here to generate the script for us -- stop cringing! Cursors are good for some things. Now because I know "oops" moments can happen I will actually script out the bind rules first in case I need them in the event they get dropped and I want to recover quickly.

Here is the bind script. Hint, change the output in Management Studio to text.

DECLARE BindRule CURSOR READ_ONLY
FOR SELECT 
o.name AS TableName
,c.name AS ColumnName
,(SELECT name FROM sys.objects WHERE object_id=c.rule_object_id) AS RuleName
FROM sys.columns AS c
INNER JOIN sys.objects AS o
ON c.object_id=o.object_id
WHERE o.is_ms_shipped <> 1
AND rule_object_id <> 0

DECLARE @TableName varchar(766)
DECLARE @ColumnName VARCHAR(766)
DECLARE @RuleName VARCHAR(255)

OPEN BindRule
FETCH NEXT FROM BindRule INTO @TableName,@ColumnName,@RuleName
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 DECLARE @message varchar(4000)
 SELECT @message = 'SP_BINDRULE ' + '''' + @RuleName + '''' + ',' + '''' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ''''
 SELECT @message = @message + char(13) 
 SELECT @message = @message + ' GO '
 PRINT @message
 END
 FETCH NEXT FROM BindRule INTO @TableName,@ColumnName,@RuleName
END

CLOSE BindRule
DEALLOCATE BindRule

GO

Neat, this almost automates the sp_bindrule statement for our database. Now our real intention here is to get rid of these rules so let's make our unbind statements.

DECLARE UnBindRule CURSOR
READ_ONLY
FOR SELECT 
o.name AS TableName
,c.name AS ColumnName
,(SELECT name FROM sys.objects WHERE object_id=c.rule_object_id) AS RuleName
FROM sys.columns AS c
INNER JOIN sys.objects AS o
ON c.object_id=o.object_id
WHERE o.is_ms_shipped <> 1
AND rule_object_id <> 0
DECLARE @TableName varchar(766)
DECLARE @ColumnName VARCHAR(766)
DECLARE @RuleName VARCHAR(255)
OPEN UnBindRule
FETCH NEXT FROM UnBindRule INTO @TableName,@ColumnName,@RuleName
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 DECLARE @message varchar(4000)
 SELECT @message = 'SP_UNUnBindRule ' + '''' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ''''
 SELECT @message = @message + char(13) 
 SELECT @message = @message + ' GO '
 --SELECT @message = @message + char(13)
 PRINT @message
 END
 FETCH NEXT FROM UnBindRule INTO @TableName,@ColumnName,@RuleName
END
CLOSE UnBindRule
DEALLOCATE UnBindRule
GO

There you have it. The output you get can be used to unbind the column rules from your database. Once the rules have been unbound you can then drop them from the database and replace them with check constraints.

Conclusion

Hopefully I have provided you with some useful scripts you can use when planning your next development iteration to aid in the removal of the deprecated rules from your database solutions. Remember, replace these rules with check constraints as Microsoft suggests and let your data values get checked in a supported fashion for a long time to come.

http://msdn.microsoft.com/en-us/library/aa258252(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa259613(SQL.80).aspx