Given the answer options, I agree that the sp_foreachtable option was the only correct one.

I agree with that completely, but it is not quite precise because triggers created on VIEW objects will remain enabled as the procedure loops only TABLE objects.

But that does not make this the recommended method to disable all triggers in a database. As can be read in the documentation that is referenced in the explanation, a better way would beDISABLE TRIGGER ALL ON DATABASE;

Not true. The command will ONLY disable all triggers scoped to DATABASE while TABLE and VIEW triggers will remain enabled.

But that does not make this the recommended method to disable all triggers in a database. As can be read in the documentation that is referenced in the explanation, a better way would beDISABLE TRIGGER ALL ON DATABASE;

Not true. The command will ONLY disable all triggers scoped to DATABASE while TABLE and VIEW triggers will remain enabled.

David in .AU (6/30/2010)Only one thing to note, this stored proc is undocumented which means it may disappear suddenly after a service pack or in new versions.

Well, there is one other thing to note: this procedure utilizes a cursor to loop through the tables. If you're using this procedure, you're probably using this in an utility procedure, and this would be acceptable.

hrvoje.piasevoli (7/1/2010)I agree with that completely, but it is not quite precise because triggers created on VIEW objects will remain enabled as the procedure loops only TABLE objects.

This would be a very good question if it was worded correctly. Given the answer options, the option to use sp_MSforeachtable was the closest to correct choice, so it was the one to choose. However, the question asked "Which of these will disable all triggers in a database?" Well, the answer is neither, because as Hrvoje and Wayne already pointed out, sp_MSforeachtable only iterates through the tables using the cursor loop. It does not iterate through views, which might have triggers defined on them. Additionally, there might be some DDL triggers in the database. If the question simply asked "Which of these will disable DML triggers on all tables in a database?" then it would be a very good question. As worded though, neither of the answer choices are sufficient to complete the task. As a matter of fact, considering the possibility of the triggers on views, there is no good way to disable DML triggers without mentioning their name. For example,

alter table [schema_name].[table_name] disable trigger all;

works well for any table, but the syntax is not suitable for views, so the syntax compliant with both tables and views should be something like this:

If I need to quickly disable all triggers in the database then I use disable trigger syntax to take care of all tables and views and then append disable trigger all on database to take care of the DDL triggers (if any). Something like this will do the trick (without any cursor loops of course):