3

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.

fenix2222
  • 4,602
  • 4
  • 33
  • 56
  • 1
    perhaps: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454 – nathan_jr Jun 20 '12 at 07:33
  • Doesn't quite do it - it seems to delete everything. In the right order, but everything nonetheless. I could really use a script like this, too; I think I'm going to tackle it when I get home this evening, if someone hasn't come up with an answer before then. My approach would be to identify records by PK/Table, recursing dependencies by FK, and then delete records from the last record upwards. Interesting challenge... – Peter Jun 20 '12 at 15:23
  • 1
    I was wrong. It does work. Just replace `%1` with the key you're trying to delete. It does, however, seem very long winded. But if it works, why fix it? – Peter Jun 20 '12 at 15:41
  • @Peter - I was half-way creating my own, I will try it and see if it works. – fenix2222 Jun 20 '12 at 22:35

1 Answers1

1

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.

fenix2222
  • 4,602
  • 4
  • 33
  • 56