You can Use INFORMATION_SCHEMA. KEY_COLUMN_USAGE and sys. foreign_key_columns in order to get the foreign key metadata for a table i.e. Constraint name, Reference table and Reference column etc.
To see foreign key relationships of a table: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name';
Here it is:
SELECT
OBJECT_NAME(f.parent_object_id) TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
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.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE
OBJECT_NAME (f.referenced_object_id) = 'YourTableName'
This way, you'll get the referencing table and column name.
Edited to use sys.tables instead of generic sys.objects as per comment suggestion. Thanks, marc_s
Another way is to check the results of
sp_help 'TableName'
(or just highlight the quoted TableName and press ALT+F1)
With time passing, I just decided to refine my answer. Below is a screenshot of the results that sp_help
provides. A have used the AdventureWorksDW2012 DB for this example. There is numerous good information there, and what we are looking for is at the very end - highlighted in green:
Try this
SELECT
object_name(parent_object_id) ParentTableName,
object_name(referenced_object_id) RefTableName,
name
FROM sys.foreign_keys
WHERE parent_object_id = object_id('Tablename')
I found this answer quite simple and did the trick for what I needed: https://stackoverflow.com/a/12956348/652519
A summary from the link, use this query:
EXEC sp_fkeys 'TableName'
Quick and simple. I was able to locate all the foreign key tables, respective columns and foreign key names of 15 tables pretty quickly.
As @mdisibio noted below, here's a link to the documentation that details the different parameters that can be used: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql
Here is the best way to find out Foreign Key Relationship in all Database.
exec sp_helpconstraint 'Table Name'
and one more way
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='Table Name'
--and left(CONSTRAINT_NAME,2)='FK'(If you want single key)
I am using this script to find all details related to foreign key. I am using INFORMATION.SCHEMA. Below is a SQL Script:
SELECT
ccu.table_name AS SourceTable
,ccu.constraint_name AS SourceConstraint
,ccu.column_name AS SourceColumn
,kcu.table_name AS TargetTable
,kcu.column_name AS TargetColumn
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
ORDER BY ccu.table_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With