Post reply

Constraint Status Checking

  • June 8, 2004 at 8:12 am

    #66086

    SQL Server allows you to enable and disable foreign key constraints by specifying the CHECK|NOCHECK options.

    e.g. "ALTER TABLE Addressees  NOCHECK CONSTRAINT FK_Addressees"

    Writing a cursor to turn off all of the foreign key constraints in a database is straightforward. However, where does SQL Server hold the information on whether a constraint has been disabled?

    I want to be able to run through a database and identify any constraints that are currently disabled, but I can't find where this information is held.

    Thanks,

    Mike.

     

     

  • June 8, 2004 at 10:17 am

    #509522

    sysreferences table contains mappings of FOREIGN KEY constraint definitions to the referenced columns and OBJECTPROPERTY returns information about objects.

    select "select ObjectProperty(OBJECT_ID('" + object_name(constid) + "'), 'CnstIsDisabled')" from sysreferences will generate statements that tells you whether foreign key constraint is disabled.

  • June 9, 2004 at 1:58 am

    #509590

    That's great.

    Thanks for the info.

  • July 6, 2004 at 3:28 am

    #513418

    I'd like to express my thanks too - I know I had done this before but I couldn't find it this morning and it was doing my head in - after a very unfruitful search on Google I was still banging my head on the desk - should have stuck here  in the first place - cheers mate.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply