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:

disable trigger [schema_name].[trigger_name] on [schema_name].[table_or_view];

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):

declare @sql varchar(max);

set @sql = cast(

(

select

'disable trigger [' + s.name + '].[' + tr.name + '] on ' +

s.name + '.' + o.name + ';' + char(10)

from sys.objects o inner join sys.schemas s

on o.schema_id = s.schema_id

inner join sys.triggers tr

on o.object_id = tr.parent_id

where o.[type] in ('U', 'V')

order by o.name, s.name

for xml path(''), type

) as varchar(max)

) + 'disable trigger all on database;';

exec (@sql);

go

Oleg