To disable one certain option of a foreign key in multiple foreign keys in one go.

Posted on 2006-04-26
Last Modified: 2008-02-26
Sql Server 2000

The following code will give me all foreign key relationship names where I have set the option "enforce relationship for replication"

SELECT OBJECT_NAME(constid) AS [Relationship Name],
    OBJECT_NAME(rkeyid) AS [Primary Key Table],
    OBJECT_NAME(fkeyid) AS [Foreign Key Table]
FROM sysreferences WITH (NOLOCK)
WHERE OBJECTPROPERTY(constid, 'CnstIsNotRepl') = 0

Now, my taks is to go to those relationship area one by one via enterprise manager and unset/untick the option "enforce relationship for replication".

Can a command line / query be generated to make my task a lot lot easier.
Question by:Mateen
    LVL 75

    Accepted Solution

    I think this is very difficult to achieve, you need to make use of dynamic sql for this..
    I think better you go with Enterprise manager

    Author Comment

    Thanks aneeshattingal

    There are may be more than one thousand foreign keys across multiple database.
    Simply the previous user / me have the habit to set the replication enforcement option.
    While I receive any dynamic sql, I have already started doing it manually.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now