Disable All Triggers

Question

Disable All Triggers

Dilip D. Kulkarni

Ten Centuries

Points: 1275
More actions
June 30, 2010 at 8:46 pm

#237165

Comments posted to this topic are about the item Disable All Triggers

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Rose Bud Hall of Fame Points: 3147 More actions · Answer 1

I did some online research to better understand how to use sp_MSforeachtable. I found this article to be very helpful:

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

It contains this very simplistic example which clarified things for me:

use pubs

go

create table #rowcount (tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

'insert into #rowcount select ''?'', count(*) from ?'

select top 5 * from #rowcount

order by tablename

drop table #rowcount

tablename rowcnt

----------------- -----------

[dbo].[authors] 23

[dbo].[discounts 3

[dbo].[employee] 43

[dbo].[jobs] 14

[dbo].[pub_info] 8

ziangij SSCrazy Eights Points: 8066 More actions · Answer 2

thank you... 🙂

i was looking for such a stuff... this can be used for anything which needs to be applied on all the tables...

viz.

sp_msforeachtable "grant select on ? to user1"

haryzohn 73724 Newbie Points: 5 More actions · Answer 3

good

ziangij (6/30/2010)
thank you... 🙂
i was looking for such a stuff... this can be used for anything which needs to be applied on all the tables...
viz.
sp_msforeachtable "grant select on ? to user1"

Downey CA Chiropractors
Chiropractors Downey CA

David in .AU SSCommitted Points: 1784 More actions · Answer 4

Only one thing to note, this stored proc is undocumented which means it may disappear suddenly after a service pack or in new versions.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Nice question, thanks! (I had to think a little since I don't use TRIGGERs much.)

udayroy15 Ten Centuries Points: 1076 More actions · Answer 6

udayroy15

Ten Centuries

Points: 1076

July 1, 2010 at 1:46 am

#1187491

very helpful syntax.......

paul s-306273 SSChampion Points: 10730 More actions · Answer 7

You'll find plenty of scripts out there using sp_MSforeachdb.

It's really useful...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Great question about an undocumented feature!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

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

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 be

DISABLE TRIGGER ALL ON DATABASE;

EDIT: The above is NOT TRUE!!! Hrovje alerted me to this mistake. This will not disable table- and view-scoped triggers, but only database-scoped DDL triggers. My apologies for the confusion.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

I try not to use undocumented procedures in anything other than a "one time" thing. I got it right, but personally I wouldn't have done it like that 😉

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

calvo SSChampion Points: 12930 More actions · Answer 11

I actually laughed a little when I saw the answers. I didn't know 'THE' was a valid t-sql command.

DROP THE TABLE <TableName>

CREATE THE TABLE <TableName>

DISABLE ALL TRIGGERS ON THE DATABASE

good question though, sp_msForEachTable is another tool in the arsenal.

thanks

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 12

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 be
DISABLE TRIGGER ALL ON DATABASE;

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

Best regards,

Hrvoje Piasevoli

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

hrvoje.piasevoli (7/1/2010)
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 be
DISABLE TRIGGER ALL ON DATABASE;
Not true. The command will ONLY disable all triggers scoped to DATABASE while TABLE and VIEW triggers will remain enabled.

Oops! You are right!

Thanks for the correction! 🙂

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Richard M. SSCertifiable Points: 7401 More actions · Answer 14

Good question and use of the undocumented sp_MSforeachdb.

Now, I have a remark here, the question stated "...disable all triggers in a database".

As Hrvoje noted above, Database scoped triggers as well as triggers on views would not be disabled.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]