Sql-server – possibility to see cascade path in MS SQL

cascadeforeign keysql server

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.

Best Answer

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.

Related Question