Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with CTE query Expand / Collapse
Author
Message
Posted Monday, December 03, 2012 9:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 AM
Points: 513, Visits: 1,123
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
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

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)





If you need to work better, try working less...
Post #1392031
Posted Monday, December 03, 2012 12:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:39 AM
Points: 818, Visits: 2,485
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

Post #1392106
Posted Tuesday, December 04, 2012 3:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 AM
Points: 513, Visits: 1,123
Thanks,
I'll try it. meanwhile I think I have managed to get it right.

Pedro




If you need to work better, try working less...
Post #1392314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse