Link to home
Start Free TrialLog in
Avatar of Mateen
Mateen

asked on

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
---------------------    -------------------    -------------------

Avatar of HuyBD
HuyBD
Flag of Viet Nam image

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'
Avatar of Mateen
Mateen

ASKER

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.
SOLUTION
Avatar of imran_fast
imran_fast

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mateen

ASKER

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.