Abfrage von Systemtabellen für mehrere Kaskadenpfade

Ich benutze Entity Framework, um meine Tische aus meinem model zu generieren, und immer wieder bekomme ich ein Problem mit mehreren Kaskadenpfaden. z.B:

Einführung von FOREIGN KEY-Einschränkung 'FK_dbo.SurveyorSurveys_dbo.Surveys_Survey_ID' auf der Tabelle 'SurveyorSurveys' kann Zyklen oder mehrere Kaskadenpfade verursachen.

Ich finde es ziemlich zeitraubend, wo die Kaskadenpfade zu diesem neuen Tisch sind, damit ich einen der Kaskadenlöcher entfernen kann. Ich denke, es wäre wirklich nützlich, wenn ich eine Abfrage auf die Systemtabellen ausführen könnte, die mir die vollständigen Pfade zeigten, die beteiligt waren. Hat jemand gearbeitet, wie man das macht?

Zum Beispiel habe ich diese sql:

SELECT ro.name referenced_object, po.name parent_object, fk.name foreign_key FROM sys.foreign_keys fk INNER JOIN sys.all_objects po ON fk.parent_object_id = po.object_id INNER JOIN sys.all_objects ro ON fk.referenced_object_id = ro.object_id WHERE delete_referential_action_desc = 'CASCADE' and (po.name = 'Surveys' or po.name = 'Surveyors'') ORDER BY ro.name 

Aber das bringt mich nur eine Ebene in der Hierarchie, es könnte besser sein, eine Kaskade löschen auf einer höheren Ebene zu entfernen, so dass ich am Ende Anpassung und Wiederholung der sql, bis ich den vollen Pfad zu arbeiten

Dies ist typischerweise etwas mit einer rekursiven Abfrage zu tun.

Zuerst ein paar Worte, um die Spaltennamen in sys.all_objects zu klären:

  • parent_object_id bezieht sich auf das object , das den Fremdschlüssel besitzt , also ist es nicht das Elternteil in der Beziehung, es ist das Kind.
  • referenced_object_id ist das übergeordnete in der Beziehung.

Wenn wir das wissen, können wir eine rekursive Abfrage schreiben, die Bäume von Fremdschlüsselbeziehungen sammelt, indem wir das Kind in der vorherigen Ebene mit dem Elternteil in der folgenden Ebene verbinden:

 WITH [Cascades] AS( SELECT fk.referenced_object_id AS [RootId], fk.parent_object_id AS [ChildId], fk.referenced_object_id AS [ParentId], fk.name foreign_key, 1 AS [Level] FROM sys.foreign_keys fk WHERE delete_referential_action_desc = 'CASCADE' UNION ALL SELECT [Cascades].[RootId], fk.parent_object_id, fk.referenced_object_id, fk.name foreign_key, [Cascades].[Level] + 1 FROM sys.foreign_keys fk INNER JOIN [Cascades] ON [Cascades].[ChildId] = fk.referenced_object_id WHERE delete_referential_action_desc = 'CASCADE' ) SELECT [root].Name AS [Root], po.Name AS [Parent], co.Name AS [Child], foreign_key, cs.[Level] FROM [Cascades] cs INNER JOIN sys.all_objects [root] ON cs.[RootId] = [root].object_id INNER JOIN sys.all_objects po ON cs.ParentId = po.object_id INNER JOIN sys.all_objects co ON cs.ChildId = co.object_id WHERE [root].name = 'Surveys' ORDER BY [Root], [Parent], [Level], [Child] 

Wenn ich die Abfrage in einer einfachen tree-Ebene Hierarchie ausführen, ist die Ausgabe:

 Root Parent Child foreign_key Level ----------------------------------- AA B1 FK_B1_A 1 AA B2 FK_B2_A 1 A B1 C FK_C_B1 2 

Alternative Sql basiert auf Gerts exzellenter Antwort. Dies lässt mich das Blatt angeben und zeigt den vollen Kaskadenpfad in jeder Zeile

 WITH Cascades AS (SELECT fk.parent_object_id, fk.referenced_object_id, fk.name foreign_keys, CAST(ro.name + ' -> ' + po.name AS nvarchar(256)) path, 1 AS level FROM sys.foreign_keys fk INNER JOIN sys.all_objects po ON fk.parent_object_id = po.object_id INNER JOIN sys.all_objects ro ON fk.referenced_object_id = ro.object_id WHERE delete_referential_action_desc = 'CASCADE' -- specifying leaf instead of root: AND po.name IN ('Surveys', 'Surveyors', 'surveyorsurveys') UNION ALL SELECT fk.parent_object_id, fk.referenced_object_id, CAST(fk.name + ' -> ' + Cascades.foreign_keys as nvarchar(128)), CAST(ro.name + ' -> ' + cascades.path AS nvarchar(256)), cascades.level + 1 FROM sys.foreign_keys fk INNER JOIN sys.all_objects po ON fk.parent_object_id = po.object_id INNER JOIN sys.all_objects ro ON fk.referenced_object_id = ro.object_id INNER JOIN cascades ON cascades.referenced_object_id = fk.parent_object_id WHERE delete_referential_action_desc = 'CASCADE') SELECT path, foreign_keys, level FROM Cascades ORDER BY path