Search code examples
sqldbcc

fixing large number of constraints issues reported by DBCC

After a bulk load with contraints check turned off I have some number of records in foreign key column to be removed because they violate constrains. DBCC indicates them nicely but is there a easy way to get violating value of this [colARef] = 'XXX' DBCC notation? I mean without parsing it, getting value to be able to look for primary key and then delete.
thanks

Solution

  • Without looking at DBCC output at all, you can find the constraint violations like:

    select  *
from    ReferencingTable t1
where   not exists
        (
        select  *
        from    ReferencedTable t2
        where   t1.ForeignKeyColumn = t2.Id
        )