SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with CTE query


Help with CTE query

Author
Message
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1568 Visits: 3317
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


PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search