Easily Find Rows That Violate Constraints

One of the known problems in addingconstraints to any kind of database is thevalidation of existing data. For this reason,DBAs often create check, foreign-key, andother types of constraints with the WITHNOCHECK clause. That way, any rows ofdata 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 CONSTRAINTck_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 WHEREsalary < 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 WHEREmgr_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 resultsin a temporary table named ##dbcc.

The DBCC CHECKCONSTRAINTSstatement's results consist of three pieces ofinformation: the name of table, the nameof the constraint, and column values thatidentify the rows violating the constraint.You can use these values in a SELECTstatement's WHERE clause. Thus, for eachconstraint-violating row, the showViolatingRows procedure uses a SELECT statementto retrieve that row's data. The procedurestores the results in a global temporary table(##tempResults) that's returned to theuser at the end of the showViolatingRowsprocedure'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 codethat demonstrates how you might use theprocedure in a script. I tested showViolatingRows on SQL Server 2005 Standard Edition and SQL Server 2000 Standard Editionfrom a Windows XP client.
—Eli Leiba