1

After going through this post, I am trying to add the following command in my stored procedure:

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

But the editor underlines it and shows this error:

SQL70005: The ALL option cannot be used to modify constraints. You must use individual constraint names instead

I'm using VS2013 Express for Web with SQL Server 2008 R2 Express. What am I missing here?

Community
  • 1
  • 1
dotNET
  • 33,414
  • 24
  • 162
  • 251
  • 1
    Seems like the error is self explanatory; you can not dsiable ALL constraints on a table in this version of SQL Server. – Richard Schneider Apr 24 '14 at 15:15
  • So which version are they talking about? The question is HUGELY popular and no one has pointed this out anywhere. – dotNET Apr 24 '14 at 15:18
  • In fact I googled the error message and didn't get any results :). Can't believe I'm the first in the world to experience this. If this behavior was changed in some particular version, this must have been documented somewhere. – dotNET Apr 24 '14 at 15:19
  • You have to use the entire command `EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"` The `EXEC sp_msforeachtable` part is important. – abhi Apr 24 '14 at 15:29
  • @abhi: Even though i'm not a stored procedures guru, I can't believe that would be the case. Any reason why u think so? – dotNET Apr 24 '14 at 15:31
  • I just tried this out in my version of sql server 2008 R2. – abhi Apr 24 '14 at 15:31
  • @abhi, he's not executing this for all tables – Andrew Apr 24 '14 at 15:31
  • and he's having SQL Server Express installed – Andrew Apr 24 '14 at 15:32
  • @dotNET, can you script your database and paste the script here? – Andrew Apr 24 '14 at 15:35
  • wow that would be a huge task. I did it the other day. the sql file was over 9MB. – dotNET Apr 24 '14 at 15:37
  • The error could be a result of having an unique constraint on the table. This is from [MSDN](http://msdn.microsoft.com/en-us/library/foof1745145-182d-4301-a334-18f799d361d1.aspx). DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled. – abhi Apr 24 '14 at 15:42
  • For me, at least, it was never causing an issue. – Andrew Apr 24 '14 at 16:04
  • Really weird. I abandoned the VS2013 editor and opened the same stored procedure in Management Studio, which didn't show any errors. I completed my stored procedure's body and saved it to the DB. Now I came back to VS2013, opened the stored procedure again and now it is not showing any errors. Doesn't make much sense. – dotNET Apr 24 '14 at 18:47
  • Check this link if it helps http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints – Algi Jul 05 '14 at 17:56

1 Answers1

0

you can write a script that will generate all the statements for you to disable each one (and hten re-enable as appropriate.

SQLDoug
  • 51
  • 3