SQL Server Disable Constraints

I don't know about you, but one of my least favorite things to do in a database is deal with foreign key constraints. It seems as though every time there is a serious data problem that requires manual intervention, I run into constraints that I am going to have to deal with before I can fix the data. Of course I know that is why they are there, but it seems to me I should be able to type in a password or something that says, "hey I know what I am doing so let me by pass the whole constraint thing." But alas, I am one who saves his wishes for bigger things so I am stuck, like most, writing or running a script that goes and drops all of the constraints and then do what I need to do and then run the script that puts them all back. This is not an awful solution and can work just fine, assuming you have an up-to-date script with all of the constraints and are sure you are not going to miss anything when putting them back. Maybe it is just me and it may be because of all of the years spent being an database administrator, but I never trust that the script has everything unless I am the one who puts things in and takes things out.

So how to fix my problem, without running a script that drops all of the constraints and then when I am done, running a script to put them back. The undocumented stored procedure of course. How else would you fix a problem that millions of users have other than to find a system procedure that doesn't officially exist. And as a bonus, not only is it an undocumented system stored procedure, it is my favorite undocumented stored procedure. How is that for dumb luck?

I don't know if I have written about undocumented stored procedures, but there are several of them and if you would like me to post them, leave a comment and I will be happy to do so. OK, back on topic.....The undocumented stored procedure can help us with our constraint problem is sp_MSforeachtable. If you are not familiar with with this proc, it does just what it sounds like it does. It runs things on each table in the database. So this proc will let me disable all constraints on the tables and then just re-enable them. Now you are probably thinking that you could just have found the tables you needed to and disabled the constraints on those tables and then enabled them and been done. You would be correct in thinking that (of course no one would know because you were thinking it and that lends it self to the whole "if a tree falls in the woods and no one is listening" thing). However, in some databases (not naming names) the database is so well normalized that the constraints can have dependencies connected to many tables. In those cases I find it easier to assume they are all going to give me a headache and just disable them all by doing the following;

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Do what I need to do to the data here and then

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Pretty easy eh? (that's a shout out to all my Canadian friends) So that is one way to deal with constraints, not necessarily better or worse than any other way, just different. Again, if you would like me to discuss sp_MSforeachtable or any other undocumented stored procedures in greater detail, email me, or leave a comment and I will be happy to do so.

Cheers! (another shout out)

-mike