[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

To find foreign keys which hase "enforce relationship for replication" is ticked.

Sql Server 2000

I want to know all foreign key names in one of database where this option is checked.
Enforce relationship for REPLICATION

Result format

Relationship_name    PrimaryKeyTable   ForeignKeyTable
---------------------    -------------------    -------------------

0
Mateen
Asked:
Mateen
3 Solutions
 
HuyBDCommented:
Try this!

select sysobjects.name as Relationship_name ,t1.name as PrimaryKey,
syscolumns.name as ForeignKeyTable,T3.name as PrimaryKeyTable,t2.name as ForeignKeyTable from sysobjects
inner join sysforeignkeys as b on  sysobjects.id=b.constid
inner join syscolumns on syscolumns.id=b.fkeyID and syscolumns.colid=b.fkey
inner join syscolumns as T1 on t1.id=b.rkeyID and t1.colid=b.rkey
inner join sysobjects as T2 on t2.id=syscolumns.id
inner join sysobjects as T3 on t3.id=T1.id
where sysobjects.xtype='F'
0
 
MateenAuthor Commented:
Hi HuyBd

The format and columns are exactly what I want.

It is also showing those foreign keys where the option "enforce relationship for replication" has been not ticked.

I want to show only all those foreign keys where the option "enforce relationship for replication"  has been ticked/set.

After seeing the result, I would remove all those replication relationship.
0
 
imran_fastCommented:
Hi Mateen!

use this
select sysobjects.name as Relationship_name ,t1.name as PrimaryKey,
syscolumns.name as ForeignKeyTable,T3.name as PrimaryKeyTable,t2.name as ForeignKeyTable from sysobjects
inner join sysforeignkeys as b on  sysobjects.id=b.constid
inner join syscolumns on syscolumns.id=b.fkeyID and syscolumns.colid=b.fkey
inner join syscolumns as T1 on t1.id=b.rkeyID and t1.colid=b.rkey
inner join sysobjects as T2 on t2.id=syscolumns.id
inner join sysobjects as T3 on t3.id=T1.id
where sysobjects.xtype='F'
AND sysobjects.STATUS < 3000                              <==== change
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
imran_fastCommented:
The above Query is applicable for sql server 2000 only.
0
 
Scott PletcherSenior DBACommented:
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
0
 
MateenAuthor Commented:
1) HuyBD : Complete work done except the line
         << AND sysobjects.STATUS < 3000   >>
        added by imran_fast.

2) ScottPletcher:
    Completely different technique.
    Usage of only one table.
   
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now