Post reply

Where is.....

  • January 16, 2008 at 5:08 pm

    #178359

    Hi,

    I'm looking to see if there is a way of determining whether or not a foreign key has

    Enforce Relationship For INSERTs and UPDATEs set using T-SQL.

    I can find out the Cascade Update/Delete Related Fields/Records using sp_FKeys (uses ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1 etc and I've tweaked the SP to include the For Replication flag in the same manner but I can't seem to determine the state of that specific option that you see in Enterprise Manager. I can replicate the actual action with an "alter table...not for replication" but I can't find the flag/status code where its stored.

    Is there a way to produce the text like sp_HelpText for a specific foreign key then I can search for WITH NOCHECK?

    Thanks in appreciation

    Jamie

    --------------------------------------------------------------------------------

    I thought I saw the light at the end of the tunnel, but it was just my manager with a torch and a stack of extra work...;)

  • January 17, 2008 at 3:11 pm

    #769474

    You may try this.

    SELECT OBJECT_NAME(rkeyid) ReferencedTable FROM sysreferences WHERE fkeyid = OBJECT_ID('ORDER DETAILS')

  • January 17, 2008 at 4:19 pm

    #769492

    Thanks for the post.

    I think however that I've found the answer in sysconstraints status field. The Higher bits have the useful nuggets that MS was not telling us about.

    So to determine if a fk is currently not enabled ( or set as ALTER TABLE [x] NOCHECK CONSTRAINT [Key] ) I have matched it against the sysreferences, sysobjects and syscolumns and set flags based on status.

    E.g.

    CASE WHEN (Status & 1024)=1024 THEN 'Constraint enforced by non clustered index'

    CASE WHEN (Status & 16384)=16384 THEN 'Constraint disabled'

    CASE WHEN (Status & 32767)=32767 THEN 'Constraint enabled'

    Thanks for the help though.

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

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