How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008) I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I replaced against the ported database.

Anyway, the legacy database doesn't have any foreign key relationships defined. My plan is to start putting them in, but leave them disabled to support the old frontends. However I want to use the disabled foreign key relationships in some kind of stored procedure to check the integrity of the database (I'll execute this as part of my integrity test suite every so often). Would that involve attempting to turn each one on, and then recording the error message, then turning it off again? Is there a special command that can do this all in one go? 

Any scripts you have to help would be nice, I'm not good at T-SQL. Ideally I'd like the script to return a table consisting of the table name, a record's primary key value, and the  name of the foreign key for that record that failed.

Thanks!
These DBCC CHECKCONSTRAINTS commands can be run as follows:


DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual
constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints
in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that
violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query
runs
DBCC CHECKCONSTRAINTS check only enabled constraints


Keep in mind that SQL Server needs to read through all of your data to check for the constraints, so be careful not to run this across the board for your large or very busy databases. This should be something that is run off hours and also for very large databases you should run this either at a table level or a constraint level.