Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Those Stubborn Database Rules - Finding Them and Scripting Them

By Scott Abrants,

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:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CHECK constraints instead. CHECK constraints are created by using the CHECK keyword of the CREATE TABLE or ALTER TABLE statements.

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

When bound to a column or a user-defined data type, a rule specifies the acceptable values that can be inserted into that column. Rules, a backward compatibility feature, perform some of the same functions as check constraints. CHECK constraints, created using the CHECK keyword of ALTER or CREATE TABLE, are the preferred, standard way to restrict the values in a column (multiple constraints can be defined on one or multiple columns). A column or user-defined data type can have only one rule bound to it. However, a column can have both a rule and one or more check constraints associated with it. When this is true, all restrictions are evaluated.

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

Total article views: 4300 | Views in the last 30 days: 2
 
Related Articles
FORUM

Select Where Any Column Equals (or Like) Value

Select row(s) where any of the column names in table equals a value

FORUM

Gap sequence of column_id

select * from sys.columns where object_id=object_id('table') returns gap in sequence of column_id

ARTICLE

The Cost of Function Use In A Where Clause

Discusses Index Selection impact when functions are wrapped around WHERE clause filtering columns

FORUM

Select premission was denied on Object

Select premission Denied on Object

FORUM

sp_executesql dynamic columns in select statement

sp_executesql dynamic columns in select statement

Tags
rules    
sp_bindrule    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones