September 12, 2023 at 3:52 pm
Hi,
Looking for a script to delete data from a table and also from the FK tables of data exists.
I have seen some online using procedures and function but just looking for a base script
September 12, 2023 at 4:54 pm
SQL itself will delete from the FK-related tables if you "tell" it to: that is, you set the ON DELETE CASCADE option on for that FK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 12, 2023 at 6:38 pm
Ok but what if my fk aren't setup with option
September 12, 2023 at 6:50 pm
If you can afford to have CASCADE in effect, drop and recreate the index with ON DELETE CASCADE specified.
Hmm, yeah, if not, we'll have some complex code to write. We can use sys.foreign_keys and sys.foreign_keys_columns, and, assuming you don't need to CASCADE thru related tables as well, that would simplify the code.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply