InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:00:04 GMT20http://www.sqlservercentral.com/Forums/Topic1392031-392-1.aspxThanks,I'll try it. meanwhile I think I have managed to get it right.PedroTue, 04 Dec 2012 03:00:05 GMTPiManéhttp://www.sqlservercentral.com/Forums/Topic1392031-392-1.aspxHi PedroThis should do what you want. I changed the DupKeys cte to use a group by and grabbed the minimum object_id as parent, then joined the DupKeys to FKColumns excluding the parent object_id[code="sql"];WITH FKColumns AS ( SELECT fk.object_id, parent_object_id, (SELECT '(' + CAST(parent_column_id AS VARCHAR(10))+ ')' FROM sys.foreign_key_columns fkc WHERE fkc.constraint_object_id = fk.object_id AND fkc.parent_object_id = fk.parent_object_id ORDER BY fkc.parent_column_id FOR XML PATH('')) parent_columns, referenced_object_id, (SELECT '(' + CAST(referenced_column_id AS VARCHAR(10))+ ')' FROM sys.foreign_key_columns fkc WHERE fkc.constraint_object_id = fk.object_id AND fkc.referenced_object_id = fk.referenced_object_id ORDER BY fkc.referenced_column_id FOR XML PATH('')) referenced_columns FROM sys.foreign_keys fk), DupKeys AS (SELECT min(fk1.object_id) parentFK_id, fk1.parent_object_id, fk1.referenced_object_idFROM FKColumns fk1GROUP BY fk1.parent_object_id, fk1.parent_columns, fk1.referenced_object_id, fk1.referenced_columnsHAVING COUNT(*) > 1)SELECT '[' + OBJECT_SCHEMA_NAME(dk1.parent_object_id) + '].[' + OBJECT_NAME(dk1.parent_object_id) + ']' TableName, '[' + OBJECT_SCHEMA_NAME(dk1.referenced_object_id) + '].[' + OBJECT_NAME(dk1.referenced_object_id) + ']' ReferencedTable, OBJECT_NAME(fk1.object_id) DuplicateFK, OBJECT_NAME(dk1.parentFK_id) OriginalFKFROM DupKeys dk1 INNER JOIN FKColumns fk1 ON fk1.parent_object_id = dk1.parent_object_id and fk1.referenced_object_id = dk1.referenced_object_id and fk1.object_id <> dk1.parentFK_id[/code]Mon, 03 Dec 2012 12:10:23 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1392031-392-1.aspxHi,I'm trying to get all the duplicate FKs on the database.I have the query that returns the data but I get "duplicate" records... Will explain "duplicate" on the code...[code="sql"]WITH FKColumns AS ( SELECT fk.object_id, parent_object_id, (SELECT '(' + CAST(parent_column_id AS VARCHAR(10))+ ')' FROM sys.foreign_key_columns fkc WHERE fkc.constraint_object_id = fk.object_id AND fkc.parent_object_id = fk.parent_object_id ORDER BY fkc.parent_column_id FOR XML PATH('')) parent_columns, referenced_object_id, (SELECT '(' + CAST(referenced_column_id AS VARCHAR(10))+ ')' FROM sys.foreign_key_columns fkc WHERE fkc.constraint_object_id = fk.object_id AND fkc.referenced_object_id = fk.referenced_object_id ORDER BY fkc.referenced_column_id FOR XML PATH('')) referenced_columns FROM sys.foreign_keys fk), DupKeys AS (SELECT '[' + OBJECT_SCHEMA_NAME(fk1.parent_object_id) + '].[' + OBJECT_NAME(fk1.parent_object_id) + ']' TableName, '[' + OBJECT_SCHEMA_NAME(fk1.referenced_object_id) + '].[' + OBJECT_NAME(fk2.referenced_object_id) + ']' ReferencedTable, OBJECT_NAME(fk1.object_id) DuplicateFK, OBJECT_NAME(fk2.object_id) OriginalFKFROM FKColumns fk1INNER JOIN FKColumns fk2 ON fk1.parent_object_id = fk2.parent_object_id AND fk1.referenced_object_id = fk2.referenced_object_id AND fk1.parent_columns = fk2.parent_columns AND fk1.referenced_columns = fk2.referenced_columns AND fk1.object_id > fk2.object_id) SELECT * FROM DupKeys dk1[/code]This returns [code="other"]TableName ReferencedTable DuplicateFK OriginalFK[dbo].[T2] [dbo].[T1] FK2 FK1[dbo].[T2] [dbo].[T1] FK3 FK1[dbo].[T2] [dbo].[T1] FK3 FK2[dbo].[T2] [dbo].[T1] FK4 FK1[dbo].[T2] [dbo].[T1] FK4 FK2[dbo].[T2] [dbo].[T1] FK4 FK3[/code]F3, F2 shouldn't show cause it exists F3, F1 and F2, F1 and so F3, F2 should be considered "duplicate" record...I can use SELECT DISTINCT TableName, ReferencedTable, DuplicateFK FROM DupKeys and it would only return the duplicate ones but I also want the "original" FK....Can any one help?Thanks,PedroPS: Code for simulating the data[code="sql"]CREATE TABLE T1 (C1 INT, C2 INT)CREATE UNIQUE INDEX T1_Ux1 ON T1 (C1, C2)CREATE UNIQUE INDEX T1_Ux2 ON T1 (C2, C1)CREATE TABLE T2 (C1 INT, C2 INT)ALTER TABLE T2 ADD CONSTRAINT FK1 FOREIGN KEY (C1, C2) REFERENCES T1 (C1, C2)ALTER TABLE T2 ADD CONSTRAINT FK2 FOREIGN KEY (C2, C1) REFERENCES T1 (C2, C1)ALTER TABLE T2 ADD CONSTRAINT FK3 FOREIGN KEY (C1, C2) REFERENCES T1 (C1, C2)ALTER TABLE T2 ADD CONSTRAINT FK4 FOREIGN KEY (C2, C1) REFERENCES T1 (C2, C1)[/code]Mon, 03 Dec 2012 09:34:33 GMTPiMané