2

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!

2
  • 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, 2012 at 22:44
  • Yeah, they will.
    – IBC
    Jun 24, 2012 at 22:58

1 Answer 1

5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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