List Foreign Keys referencing tables in SQL Server

22 September,2022 by Tom Collins

Question: How can I list all Foreign Keys referencing tables and columns within SQL Server tables? 

 

Answer: This query will return 6 columns

FOREIGN_KEY = Name of Foreign Key

SCHEMA_NAME = Name of Schema

TABLE = name of table

COLUMN = Column name 

REFERENCED_TBL = the table that has the key column that your FOREIGN_KEY is pointing to

REFERENCED_COL = The column that is the key that your FOREIGN_KEY is pointing to

kkk

 

 

 

use [mydb]

SELECT  obj.name AS FOREIGN_KEY,
    sch.name AS [SCHEMA_NAME],
    tab1.name AS [TABLE],
    col1.name AS [COLUMN],
    tab2.name AS [REFERENCED_TBL],
    col2.name AS [REFERENCED_COL]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
	order by tab1.name

 

Read More on SQL Keys 

How to drop a SQL column and find foreign keys with sp_fkeys

Natural key versus Surrogate key on a 1 column table

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on List Foreign Keys referencing tables in SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer