My database structure kind of looks like a tree - a main root table with a primary key, tables with foreign keys back to that table, other tables with foreign keys back to the 2nd level, etc. All of my tables (well, for the purpose of this question anyway) are eventually related back to the root through a foreign key relationship. Now, I need to move all the records associated with a specific row in the root table to another database along with all of its associated records down the tree. And I'd like it to be dynamic so that it doesn't have to change as the DB structure is modified. It didn't seem to bad at first, just follow the keys! But it quickly became complex as I realized that some tables might be self-referencing, some have multi-column keys, some tables are optimized for performance and thus have circular key references, and the order of delete (for the cleanup) doesn't necessarily follow the order I discovered the rows in by following the key structure.
So... I've done some searches, but haven't found anyone who has done something similar that I can leverage and make sure I haven't missed anything. Have you seen anyone do something similar (and can point me to it)?