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

Posted on 2006-04-26
Medium Priority
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

Aneesh Retnakaran earned 2000 total points
ID: 16550099
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

ID: 16550180
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

839 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