Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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!

share|improve this question
Will your tables have primary keys or unique keys that you can use as references for the foreign keys? You won't be able to build these disabled foreign keys (in your example) without them. – 8kb Jun 24 '12 at 22:44
Yeah, they will. – IBC Jun 24 '12 at 22:58

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
this appears to be what i need. It can even check disabled constraints and it returns error messages for each row. Thanks. I'll accept your answer when I've completed the script. – IBC Jun 25 '12 at 17:29
For me the answer was: exec sp_MSforeachtable 'DBCC CHECKCONSTRAINTS("?") WITH ALL_CONSTRAINTS' – IBC Jun 26 '12 at 7:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.