Database rules in SQL Server can be a powerful tool for enforcing data integrity and ensuring that only valid data is inserted into a column. However, these rules are being deprecated in future versions of SQL Server, and it is recommended to replace them with CHECK constraints. In this article, we will explore how to identify and script out these hidden rules, and how to replace them with check constraints.

Identifying Database Rules

Finding rules that are bound to columns is not an easy task. While you can see the rules listed under Programming\Rules in Management Studio, finding where and what they are bound to is the other half of the story. To identify these hidden rules, you can write a query against the system views. For example:

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

This query will show the rules, columns, and the table they are bound to in the database.

Replacing Database Rules with CHECK Constraints

Once you have identified the rules, it is important to replace them with CHECK constraints. CHECK constraints are created using the CHECK keyword of the CREATE TABLE or ALTER TABLE statements. They allow you to specify the acceptable values that can be inserted into a column. Multiple constraints can be defined on one or multiple columns.

To replace the rules, you can generate a script that will unbind the rules from the columns. For example:

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_UNBINDRULE ' + '''' + QUOTENAME(@TableName) + '.' + QUOTENAME(@ColumnName) + ''''
        SELECT @message = @message + char(13)
        SELECT @message = @message + ' GO '
        PRINT @message
    END
    FETCH NEXT FROM UnBindRule INTO @TableName,@ColumnName,@RuleName
END

CLOSE UnBindRule
DEALLOCATE UnBindRule

This script will generate the statements 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

In this article, we have discussed how to identify and replace database rules in SQL Server. It is important to replace these rules with check constraints as recommended by Microsoft to ensure data integrity and compatibility with future versions of SQL Server. By understanding and scripting out these hidden rules, you can effectively plan the removal of deprecated rules from your database solutions.