I have a delete issue at the moment. We do not have permission to enable Cascade delete on the record. I have User table where userId is references in about 20 different table. I want to be able to write a dynamic query which would go and recursively find all tables that reference userId and delete those (for each table it will have to find its own foreign key and delete them as well), so basically a recursive function/procedure which would take table name as a parameter and primary key id and will perform cascade delete. Any script that can do that? I can run a query on information_schema to retrieve one level of dependencies for User table, but I need to somehow recursively find all dependencies of the linked tables as well to be able to wipe the record.
As suggested by "Nathan Skerl" I used this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454 and it did the job. The only thing is that I modified fnCascadingDelete function to include RecordID parameter so you can pass id of the record you want to delete then I searched for %1 and replaced it with @RecordID. It works perfectly, a bit slow for very deep nesting but I guess it is expected.