Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
Hi
I just want to see the foreign key reference of a table, say 'foo'. I can check the dependencies and all but I don't know the refered tables with foo. Is there any query for that??? Thanks in advance.
Posted 12-Feb-13 20:52pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

SELECT OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id)
    FROM sys.foreign_keys
    WHERE referenced_object_id = OBJECT_ID(@mytable)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi,
 
Try This Query
 

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='Your_Table_Name'
ORDER BY 1,2,3,4
 

Regards,
GVPrabu
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I searched for some possible query to find the refernces but I couldn't able to find one. So I tried deleting the primary key of a table and automatically the sql showed all the references of that table in a dialogue box. It helped.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 495
1 Maciej Los 340
2 Richard MacCutchan 265
3 BillWoodruff 225
4 Mathew Soji 200
0 OriginalGriff 8,804
1 Sergey Alexandrovich Kryukov 7,457
2 DamithSL 5,689
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100