Delete Cascade without explicitly referring to all dependencies

Question

I'm building a small db-cleaner app for a QA sql server database. Naturally, I need to delete table rows with dependencies on them.

T-SQL cascading abilities are very limited, so I've tried using NHibernate to simplify matters. But, the only way I found for this was to create a collection for each dependency in the object-to-delete, and mark that as cascade=delete.

That means creating many, many collections (both in the hbm file and in the C# object) which I don't need for any other purpose. Which makes this method as complicated as just using SQL.

Am I missing something? Is there any easier, more generic way to perform delete-cascade?

Thanks.

EDIT: Just to be clear, I avoid changing the foreign keys in the DB because it's a QA DB, designed to be identical to the production DB.

Answer 1

Eventually I found out a generic way to do the deletion:

This guy wrote a recursive SP which does all the work for you:

http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7

Needed a little touch-ups (since my DB uses schemas) but works like a charm.