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.