Find cascading deletes that effect a specific table

Question

I'm having problems with my SQL Server based application (C#/Winforms). On more than one occasion, a user has complained that a certain key record has deleted itself. After some investigation, it seems they are right - there are clues that the record once existed (one other table has referneces to the missing record). Also, it seems that this was not accidental deletion by a user (I have other functionality which would catch this out).

I'm now trying to work out if the record is being deleted at the SQL level. If somehow, by deleting a record from a related table, it had deleted a record from another table which it should not.

So, I'm looking for some way to investigate this. An easy way to point at a table, and query the system to tell me what events/triggers (like a cascading delete) could delete a record in this specific table?

Answer 1

Check this query

SELECT C.*
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
WHERE DELETE_RULE = 'CASCADE' 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

in sql server

right click on the tablename -> script table as -> create to -> New query editor window.

now you can see wether the table has any cascade delete or not

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 3

in sql server

right click on the tablename -> script table as -> create to -> New query editor window.

now you can see wether the table has any cascade delete or not

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Thanks. This did the trick I think. I found a few which had "ON DELETE SET NULL" in them, but none that delete the record.

I guess I'll keep looking within the app.