Click here to Skip to main content
12,077,911 members (48,301 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160212.1 | Last Updated 21 Feb 2013
Copyright © CodeProject, 1999-2016
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