3

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.

TGlatzer
  • 131
  • 5

1 Answers1

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.

David Spillett
  • 29,657
  • 3
  • 43
  • 82
  • That's not the answer I was looking for, since it's manually querying the FKs, but it's helpful. Thanks :) – TGlatzer Nov 20 '17 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. – David Spillett Nov 21 '17 at 01: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 Nov 21 '17 at 07:59