Detection of Broken Forign Key Relationship

Under special circumstances, it could happen that database contains "orphan" records. Data in dependant table exists without it's "parent" record in a master table. It could be caused by a bulk insert operation without CHECK_CONSTRAINTS option, or by disabling foreign key for a while or adding a foreign key to a table that already contains data without checking existing data for validity.

In SQL2000, there is a function DBCC CHECKCONSTRAINTS, that checks all tables (or just a chosen one) for consistency with all (or chosen) constraints. (actually, there was a bug in version 8.00.194 that didn't allow to run this statement against database with special character eg. space in a table name eg. Northwind database :-)).

As far as I know there is no similar functionality in MS SQL7.

If you would like to check your data, you can try following SP. It has one optional parameter for a name of a table. If you don't specify the name, all the user tables in a current database will be checked. The output format is similar to DBCC CHECKCONSTRAINTS.

If you need to check just broken FOREIGN KEY relationship, enclosed stored procedure runs faster than DBCC.