CODEWITHSUNDEEP
sql-server
JemWritesCode
JemWritesCode

Reputation: 521

How to find what table foreign key is from?

I have a table with a foreign key. How can I tell what table the FK is a primary key in? There's about 200 tables and I don't know how to find where that info is coming from/connected to.

Upvotes: 0

Views: 899

Answers (3)

Tab Alleman
Tab Alleman

Reputation: 31775

Either of the answers by NoDisplayName or Kris G. should work, but if you want something easier to remember while you're in SSMS, just right click the Foreign Key and choose Script As>Create To>New Window.

You will then get a script that can be used to (re-)create the FK, and you will be able to see what column it references in what table by reading the script.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Use this.. 

SELECT fk.name,
       Object_name(fk.parent_object_id) [Parent table],
       c1.name                          [Parent column]
FROM   sys.foreign_keys fk
       INNER JOIN sys.foreign_key_columns fkc
               ON fkc.constraint_object_id = fk.object_id
       INNER JOIN sys.columns c1
               ON fkc.parent_column_id = c1.column_id
                  AND fkc.parent_object_id = c1.object_id
       INNER JOIN sys.columns c2
               ON fkc.referenced_column_id = c2.column_id
                  AND fkc.referenced_object_id = c2.object_id
WHERE  Object_name(fk.referenced_object_id) = 'Tablename' -- Replace with your tablename
       AND c2.name = 'Columname' -- Replace with your columname

Or simply use 

sp_help Tablename or [Alt]+F1

Upvotes: 2

Kris Gruttemeyer
Kris Gruttemeyer

Reputation: 872

This should help. Just run it in the DB you wish to query:

SELECT f.NAME AS ForeignKey
    ,SCHEMA_NAME(f.SCHEMA_ID) SchemaName
    ,OBJECT_NAME(f.parent_object_id) AS TableName
    ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
    ,SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName
    ,OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName
    ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
GO

Source: http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

Basically, the first column is the FK, followed by the FK schema and object. Following those are the PK column name, its schema and object.

Upvotes: 2

Related Questions