deleting dependent rows in SQL 2000

I have a contacts table that has around 30 tables related to it (which
have other tables related to it - that often refer back to the same
contacts table). I am trying to delete a single contact and all rows
in any table related to it. I wrote a recursive query to scan the
database and remove the corresponding rows before deleting the contact
row.
I'm not sure this is the best solution. It takes a great deal of time
to run and seems to have problems with certain sections of the
database. Another idea I had was to modify each of the foreign keys
to turn on Cascade Delete whenever possibe, delete the row in the
contacts table then turn Cascade Delete off.
I would simply write the query out by hand but, because this is one of
the core tables, any new tables are usually linked to it and usually
kill the script (forcing yet another rewrite). Anyone have any
suggestions on what the best method would be to do this? Thanks for
your help.
AlexAlex,
Yes, you've got to tear them down in order of hierarchy.
DELETE GREATGRANDCHILDREN
DELETE GRANDCHILDREN
DELETE CHILDREN
DELETE PARENT
No shortcuts.
The good new is, once you get it written, you shouldn't have to write it
again. ;-)
James Hokes
"Alex" <under_payd@.yahoo.com> wrote in message
news:64b52496.0402231433.10fdcf5a@.posting.google.com...
> I have a contacts table that has around 30 tables related to it (which
> have other tables related to it - that often refer back to the same
> contacts table). I am trying to delete a single contact and all rows
> in any table related to it. I wrote a recursive query to scan the
> database and remove the corresponding rows before deleting the contact
> row.
> I'm not sure this is the best solution. It takes a great deal of time
> to run and seems to have problems with certain sections of the
> database. Another idea I had was to modify each of the foreign keys
> to turn on Cascade Delete whenever possibe, delete the row in the
> contacts table then turn Cascade Delete off.
> I would simply write the query out by hand but, because this is one of
> the core tables, any new tables are usually linked to it and usually
> kill the script (forcing yet another rewrite). Anyone have any
> suggestions on what the best method would be to do this? Thanks for
> your help.
> Alex