A few weeks back I was working on a process that would utilize foreign keys to crawl from table to table to export data from a production database to an archive database.  More on some of what went into that process probably at some point in the future.

When the project was released to production a strange issue occurred.  I was getting duplicates in a few of the tables that previously didn’t cause duplicates to occur.  I went through the code and was able to determine that the issue was tied to duplicated INSERT statements that derived from foreign keys.

To solve the issue I ended up creating the query below that reviewed the foreign keys on the tables to determine if there were duplicates in the database.

;WITH ForeignKeys1 AS
(
SELECT fk.object_id
,OBJECT_NAME(fk.parent_object_id) as table_name
,fk.name as foreign_key_name
,fk.create_date
,(SELECT CAST(parent_object_id as varchar(50))
+ SPACE(1) + CAST(parent_column_id as varchar(50))
+ SPACE(1) + CAST(referenced_object_id as varchar(50))
+ SPACE(1) + CAST(referenced_column_id as varchar(50)) as [data()]
FROM sys.foreign_key_columns fkc
WHERE fk.object_id = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR XML PATH('')) foreign_key
FROM sys.foreign_keys fk
)
,ForeignKeys2 AS
(
SELECT object_id, table_name, foreign_key_name, foreign_key
,ROW_NUMBER() OVER (PARTITION BY foreign_key ORDER BY create_date) as ForeignKeyRank
FROM ForeignKeys1
)
SELECT x.table_name as [Table Name]
,x.foreign_key_name as [Foreign Key]
,y.foreign_key_name as [Foreign Key Duplicate]
FROM ForeignKeys2 x
INNER JOIN ForeignKeys2 y ON x.foreign_key = y.foreign_key AND x.ForeignKeyRank > y.ForeignKeyRank

Hope someone else finds some use out of this.