Deletes From Multiple Tables.

Hello everybody,

We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.

Is it possible? Please advice.

Thanks in advance.

Regards,

Ashishuse "Cascade Delete"...|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.

Even wrtting a trigger to delete from all the tables will mean writing some kind of loop or Cursor if we use the system tables.
I can use sys tables but the delete qury using sys tbales needs to execute in 1 go. No cursors or temp tables should be used.

Is there any other way?|||I'm sorry i forgot to mention that the Cascading Deletes are not allowed.
Frickin' morons.
My advice is to go work for a company that isn't managed by idiots.|||Now there's the blind dude we all know and love|||No cascading deletes allowed.
No temp tables allowed.
No cursors allowed. (Well, at least they are on the right track with that one...)

Absolute amateurs.

What advice would you give them?|||What advice would you give them?

Well, it realy sounds like a request for homework...but

DECLARE @.FKey sysname, @.keyvalue varchar(8000), @.sql varchar(8000)
SELECT @.FKey = 'EmployeeID', @.keyValue = '1', @.sql = ''

SELECT @.sql = @.sql + 'DELETE FROM ['+o.name+'] WHERE '+@.FKey+'='+@.KeyValue+' GO '
FROM sysreferences r
LEFT JOIN sysobjects o
ON r.fkeyid = o.id
WHERE rkeyid = OBJECT_ID('Employees')

SELECT @.sql

--EXEC(@.sql)|||Thanks dear friends.

Brett, it's not a homework at all.

In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

Me being the modest developer cannot argue with the people who have set these guidelines. Please forgive those poor souls for their ignorance.

Thanks once again. :)|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things
Using cascading deletes is the best solution for this problem (and is supported by all major DBMS).
If the "world largest" project does not apply the most effectice solutions to such a basic problem, then I'm pretty sure it is going to fail. This is extremely short-sighted and will create more maintenance trouble then they probably are willing to pay for. Why not use flat files, if they don't want to use the features of a database?
(nothing personal against you wash, it's just that I see this kind of stupid decisions all around...)|||Man, i don't mind it as long as they are paying me for the job. [:D]

I do not exactly know what has prompted the TAs to avoid cascade deletes.|||In fact, i'm working on the one of the largest .Net implementations in the world. But somehow we have some basic guidelines which do not allow certain things.

reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.|||reading this made think he was working for the US federal government. in which case it may very well fail. The FBI can not get their case management software together, the IRS has been struggling for years to modernize.

Forgive me but i don't work on any such project.|||I have had everyone in my team at my company, including the so called SQL experts working with me, brainstorm on this issue.

It seems without a recursive loop , we cannot achieve this.

Can you give me any idea if we can achieve this using a simple loop.

I want to delete all the dependent records to the Nth level.

Thanks in advance.|||HAve one of your "sql experts" get in on this thread...and did you even look at my code?|||What in the world would drive such requirements? It makes as much sense as "We'd like to retrieve all the data from the database but I can't reference any table names or use the word 'select'".

I'm also guessing that you're going to generate a heap of log activity with this puppy, so you might want to think about doing a set of tables at a time or a set of primary key values, unless you own unlimited disk.|||Thanks Brett. i did use your code but it useful only for the first level of dependencies.

Thankfully people have realized what kind of issues have cropped up due to such requirement and thankfully we are working on some realistic solution.

I would like to thank everyone for chipping in with helpful suggestions and valuable advice.

thanks once again.