SQL SERVER – 2008 – Find Relationship of Foreign Key and Primary Key using T-SQL – Find Tables With Foreign Key Constraint in Database

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)

,
Previous Post
SQLAuthority News – Author Visit – MVP Global Summit 2009 – Seattle and Redmond
Next Post
SQLAuthority News – MVP Summit 2009 – Journey Begins

Related Posts

26 Comments. Leave new

Leave a Reply

Menu