November 19, 2004 at 8:25 am
I have a database with over 10,000 tables and about 200 of those tables have cascade deletes turned on, does anyone know of any way through T-SQL to turn off all the cascading deletes in 1 fell swoop? Thanks in advance.
November 19, 2004 at 8:56 am
You'll have to drop all foreign keys and recreate them. I have no ready made script for that. But maybe someone here has one.
November 19, 2004 at 9:20 am
I was hoping someone already had a script to do it.
November 19, 2004 at 9:35 am
this is a good start :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=144674
this script (last post of the thread) will allow you to generate all the create DDL. You can inspire yourself from this to generate all the drops, then you'll just have to rn the drop everything and the recreate scripts.
P.S. Don't forget the with no check option (not included in the script) so that the foreign keys intergrity is not reevaluated will running this script.
November 19, 2004 at 4:09 pm
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Quand on parle du loup, on en voit la queue
November 19, 2004 at 4:11 pm
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Turns them back on.
Quand on parle du loup, on en voit la queue
November 20, 2004 at 4:08 pm
He doesn't want to disable them he wants to remove the on delete cascade option and there's no option but to redo the constraints.
November 20, 2004 at 6:03 pm
you are correct, I want to remove the on delete cascade
November 22, 2004 at 8:52 am
Oh, jumped the question. Yes, you will have to redo the foreign keys.
Quand on parle du loup, on en voit la queue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply