John Morehouse shows how you can piece together foreign key relationships in SQL Server:
Recently, I had to purge some parent records from a table. In this case, the parent table had foreign keys, which itself isn’t an issue. The fact that there were more than 30 of them was. While SQL Server will happily tell you that you are violating a foreign key if a child record is present when deleting the parent record, finding all of them can be cumbersome. This is even more true when you have a larger number of foreign keys.
Thankfully, SQL Server can tell us a lot of information about foreign keys including both the parent and child tables as well as the column used. From this information, we can dynamically create a SELECT statement that would tell us the number of child records that are tied to the parent ID.
Click through for the solution.