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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
26 Comments. Leave new
Thanks Dave, I was looking for this…helped me a lot about the dependency.
Thanks a lot.Really appreciate all the stuffs that you put in to enrich the SQL world.
Very Nice. Exactly what I was looking for.
Thank you very much Dave
Issam El Abouri – Your welcome!