While searching for how to find Primary Key and Foreign Key relationship using T-SQL, I came across my own blog article written earlier SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database. It is really handy script and not found written on line anywhere. This is one really unique script and must be bookmarked. There may be situations when there is need to find out on relationship between Primary Key and Foreign Key.
I have modified my previous script to add schema name along with table name. It would be really great if any of you can improve on this script.
USE AdventureWorks;
GO
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
Reference : Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
nice script!
Always fun to turn a script for 1 DB to ALL DB’s
sp_MSForEachDB ‘
if ”?” NOT IN (”tempdb”,”master”,”model”,”msdb”) USE ?
SELECT
DBName = DB_NAME()
,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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id ON f.OBJECT_ID = fc.constraint_object_id
‘
Easily, the article is in reality the sweetest on this laudable topic. I agree with your conclusions and will eagerly look forward to your approaching updates. Just saying thanks will not just be enough, for the tremendous lucidity in your writing. I will immediately grab your rss feed to stay privy of any updates. Good work and much success in your business efforts!
Pinal – You got me. (From your script)How is follow script able to work? An inner join without any clause and another with two conditions not seperated by any operator. Please explain.
…
INNER JOIN
sys.foreign_key_columns AS fc
INNER JOIN
sys.objects AS o
ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
Hello Mac,
If I remove the alias then the query would like following:
FROM sys.foreign_keys
INNER JOIN sys.foreign_key_columns
INNER JOIN sys.objects
ON sys.objects.OBJECT_ID = sys.foreign_key_columns.referenced_object_id
ON sys.foreign_keys.OBJECT_ID = sys.foreign_key_columns.constraint_object_id
Notice that both ON clause are on different tables.
Regards,
Pinal Dave
Hi..
i am facing a problem while retreiving foreign keys.
i am passing foreign key table in below your query but i returns some extra columns or you can say some of its column’s data is beign duplicate.
how can i handle this?
thnxk
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
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ON f.OBJECT_ID = fc.constraint_object_id
my database version is 2000.
Sorry i sent a wrong query . i am trying this one:
select ISNULL(c.constraint_name,”) ConstraintName,
ISNULL(cfk.table_name ,”) FK_Table_name,
ISNULL(kcu.column_name ,”) FK_Column_name,
ISNULL(cpk.table_name ,”) PK_Table_name,
ISNULL(pkt.column_name ,”) PK_COlumn_name
from information_schema.REFERENTIAL_CONSTRAINTS c
INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name
INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name
INNER JOIN
(
SELECT tci.table_name, kcui.column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui on tci.constraint_name = kcui.constraint_name
WHERE tci.CONSTRAINT_TYPE = ‘PRIMARY KEY’
) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
GROUP BY c.constraint_name,cfk.table_name, kcu.column_name, cpk.table_name, pkt.column_name
HAVING count(pkt.column_name) = 1 and
cfk.table_name = ‘bp_bank’
ORDER BY 1,2,3,4
Thanks a lot. It was a great help for me.
Regards.
This was great. I was wondering though..can you provide an example where if a relationship exists…
Drop the relationship/contstraint
What I want to do is create table and its relationships, but if I need to drop the parent table, I want to drop the respective relationships before hand.
Thanks.
Dave,
Your knowledge and examples are great. I get just the answers I’m looking for.
Thank you, thank you, thank you!
Thanks, this was handy
Thanks Dave. Nice script to have. I just added a where clause so that I can filter by table name.
WHERE OBJECT_NAME(f.parent_object_id)= ‘tablename’
Pinal, I often come across your blog when trying to solve a SQL issue. Thank you so much for all of the work you do to provide such a great resource! It is much appreciated
Hi Pinal,
I am a beginner in SQLServer.i want to update and delete more then two tables
using merge concepts.so kindly let me send query asap….
Note:Tables are relate with foreign key,primary key constrains..
Thanks,
Hi Pinal,
This is my sample tables…
Table1
id name country
1 xyz US
2 xyz India
————————–
Table2
id aaa bbb ccc
1
2
—————————
Table 3
id xx yy zz
1
2
i want tables like this…
Table1
id name country
2 xyz India
————————–
Table2
id aaa bbb ccc
2
2
—————————
Table 3
id xx yy zz
2
2
how to get recently inserted record in a table
by foreign key how can i get the original table???
If I wanted to find all the possible joins (ie. Inner, left or right) as it pertains one table to another table, what SQL query in 2005 would be the best to use? I am trying to print out a data schema for relationship between all tables. It would make it easier for querying the database? Need help. Any help I would greatly appreciate. Thank you!