June 8, 2004 at 8:12 am
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
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
That's great.
Thanks for the info.
July 6, 2004 at 3:28 am
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