Often developers come to me wanting cascade deletes implemented through foreign key definitions. With this stored procedure I can quickly show them the child tables involved and which ones need changes in their foreign key definitions from 'no action' to 'cascade'.
Create the ShowDeleteTree procedure in any database and call it with a table name for example:
exec ShowDeleteTree 'PurchaseOrderHeaders'
Use text output so you can see the tree of foreign key tables involved. You may optionally pass a number from 1 to 9 of the depth you want to see. This limits output to something manageable since larger databases often result in pages of output.
Most databases contain a mixture of child deletes through triggers and through foreign keys. This procedure will help with the declarative referential integrity half. You'll still need to look through trigger code for the other half.
The output below is from running the following command in msdb:
exec ShowDeleteTree DTA_reports_database