Disable All Triggers

  • Dilip D. Kulkarni

    Ten Centuries

    Points: 1275

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

  • Rose Bud

    SSCrazy

    Points: 2970

    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

    SSCertifiable

    Points: 6958

    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

    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"

  • David in .AU

    SSCommitted

    Points: 1784

    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

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

  • udayroy15

    Ten Centuries

    Points: 1076

    very helpful syntax.......

  • paul s-306273

    SSChampion

    Points: 10589

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

    It's really useful...

  • Koen Verbeeck

    SSC Guru

    Points: 258942

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

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

    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

    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

    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

    Hrvoje Piasevoli

  • Hugo Kornelis

    SSC Guru

    Points: 64645

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

    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]

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

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