December 3, 2012 at 9:34 am
Hi,
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...
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) OriginalFK
FROM FKColumns fk1
INNER 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
This returns
TableNameReferencedTableDuplicateFKOriginalFK
[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
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,
Pedro
PS: Code for simulating the data
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)
December 3, 2012 at 12:10 pm
Hi Pedro
This 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
;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_id
FROM FKColumns fk1
GROUP BY fk1.parent_object_id, fk1.parent_columns, fk1.referenced_object_id, fk1.referenced_columns
HAVING 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) OriginalFK
FROM 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
December 4, 2012 at 3:00 am
Thanks,
I'll try it. meanwhile I think I have managed to get it right.
Pedro
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply