Easily Find Rows That Violate Constraints

One of the known problems in adding constraints to any kind of database is the validation of existing data. For this reason, DBAs often create check, foreign-key, and other types of constraints with the WITH NOCHECK clause. That way, any rows of data that violate the constraint are ignored.

When data validation is important, DBAs must find all the constraint-violating rows so that they can fix them. To find them, they have to use a different SELECT statement for each constraint, which is a tedious task. For example, suppose a table has these check and foreign-key constraints:

ALTER TABLE emp ADD CONSTRAINT
ck_emp_sal CHECK
(salary BETWEEN 4000 and 10000)
  
  ALTER TABLE emp ADD CONSTRAINT  
  FK_emp_mgr FOREIGN KEY 
(mgr_id) REFERENCES emp (emp_id)
  

First, the DBA has to run the following SELECT statement to retrieve the rows that violate the check constraint:

SELECT * FROM emp WHERE
salary < 4000 OR salary > 10000

Then, the DBA has to run the following SELECT statement to retrieve the rows that violate the foreign-key constraint:

SELECT * FROM emp WHERE
mgr_id NOT IN 
(SELECT emp_id FROM emp)

I wrote a procedure called showViolatingRows to automate the tedious task of finding rows that violate check and foreignkey constraints. This procedure needs only one piece of input: the constraint name that uniquely identifies the table it's declared in.

As the excerpt in Listing 1 shows, the showViolatingRows procedure dynamically activates the DBCC CHECKCONSTRAINTS statement. This statement checks the integrity of a specific constraint or all the constraints for a specified table. The showViolatingRows procedure stores the DBCC CHECKCONSTRAINTS results in a temporary table named ##dbcc.

The DBCC CHECKCONSTRAINTS statement's results consist of three pieces of information: the name of table, the name of the constraint, and column values that identify the rows violating the constraint. You can use these values in a SELECT statement's WHERE clause. Thus, for each constraint-violating row, the showViolatingRows procedure uses a SELECT statement to retrieve that row's data. The procedure stores the results in a global temporary table (##tempResults) that's returned to the user at the end of the showViolatingRows procedure's execution.

On the SQL Server Magazine Web site, you'll find a file named showViolatingRows.sql, which includes the code for the showViolatingRows procedure as well as code that demonstrates how you might use the procedure in a script. I tested showViolatingRows on SQL Server 2005 Standard Edition and SQL Server 2000 Standard Edition from a Windows XP client.
—Eli Leiba