4

I'm having trouble with creating a foreign key in a database, because of multiple cascade paths.
I am aware of some of my FKs cascading the delete, but I cannot traverse back to find the multiple cascade path.

So I'm trying to find a way to caclulate the actual cascade paths of my database. Is there anything besides manually querying the FKs to show the paths?

Inspired by @David Spillett I forged a small script, which will at least Show all references:

SELECT
    FK.name,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name AS PARENT,
    RSCH.name + '.' + RTBL.name + '.' + RCOL.name AS REFERENCED,
    PSCH.name + '.' + PTBL.name + '.' + PCol.name + ' -> ' + RSCH.name + '.' + RTBL.name + '.' + RCOL.name
FROM 
    sys.foreign_keys FK
JOIN 
    sys.foreign_key_columns FKC
    ON FK.object_id = FKC.constraint_object_id
JOIN sys.tables AS PTBL
    ON FKC.parent_object_id = PTBL.object_id
JOIN sys.schemas as PSCH
    ON PTBL.schema_id = PSCH.schema_id
JOIN sys.columns AS PCOL
    ON FKC.parent_column_id = PCol.column_id
    AND FKC.parent_object_id = PCol.object_id

JOIN sys.tables AS RTBL
    ON FKC.referenced_object_id = RTBL.object_id
JOIN sys.schemas as RSCH
    ON RTBL.schema_id = RSCH.schema_id
JOIN sys.columns AS RCOL
    ON FKC.referenced_column_id = RCol.column_id
    AND FKC.referenced_object_id = RCol.object_id

WHERE
    FK.delete_referential_action = 1

With a Little CTE-Magic, this could probably be turned into something which Shows all cascade paths.

1 Answer 1

1

You can read information about FKs from sys.foreign_keys. The object_id and referenced_object_id will be references to rows in sys.tables (or sys.objects) so you can pull out the names of the tables involved. If you need more detail add in sys.foreign_key_columns and sys.columns.

A simple query on those might be enough to tell you what you need, or you could use a recursive CTE to generate a complete map of the tree of relationships.

3
  • That's not the answer I was looking for, since it's manually querying the FKs, but it's helpful. Thanks :)
    – TGlatzer
    Commented Nov 20, 2017 at 14:39
  • Sorry, by "not manually" I thought you meant checking SSMS or trying to create things and waiting for errors. Querying the views in the sys schema is probably as non-manual as you are going to get without an extra tool. Commented Nov 21, 2017 at 1:05
  • Oh no Problem, your answer made me able to write the script above (btw: linking all the docs is really neat) Perhaps I'll find time to create an answer, so the script shows all the paths like TblA.ColA -> TblB -> TblC -> TblD, etc.
    – TGlatzer
    Commented Nov 21, 2017 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.