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.

                                 (or)

SELECT PK.TABLE_NAME,PK.CONSTRAINT_NAME ,FK.CONSTRAINT_NAME FROM
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK       INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FKON

     PK.CONSTRAINT_NAME=FK.UNIQUE_CONSTRAINT_NAME
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) ASReferenceColumnNameFROM sys.foreign_keys AS fINNER JOIN sys.foreign_key_columns AS fcINNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_idON f.OBJECT_ID = fc.constraint_object_id