Disable / Enable All triggers on all tables in sql server 2005

• tsepo

  Old Hand

  Points: 303

  More actions

  April 14, 2010 at 7:43 pm

  #233883

  Comments posted to this topic are about the item Disable / Enable All triggers on all tables in sql server 2005

• Jamie Burmeister

  SSC Journeyman

  Points: 75

  More actions

  April 18, 2010 at 11:50 pm

  #1153595

  Hi there,

  Another way that I use which I think will do the same thing is:

  --Disable all triggers

  sp_msforeachtable "ALTER TABLE ? Disable TRIGGER all"

  Then when your ready to enable them again:

  --Enable all triggers

  sp_msforeachtable "ALTER TABLE ? Enable TRIGGER all"

  I've used this plenty of times in SQL2005 and although I haven't tried in 2008 as yet, I expect it will work.

• tsepo

  Old Hand

  Points: 303

  More actions

  April 18, 2010 at 11:59 pm

  #1153597

  Thanks Jammie,

  That will definately work as well in both 2005 and 2008 as the system stored procedure sp_msforeachtable is included in the latest version, and intelli sense actually pick it up, well spotted.

• Abrar Ahmad_

  SSCarpal Tunnel

  Points: 4246

  More actions

  April 19, 2010 at 1:06 am

  #1153618

  [font="Verdana"]

  SELECT 'ALTER TABLE ['+ SC.NAME+'].[' + PB.NAME + '] ENABLE TRIGGER ALL'

  FROM SYS.TRIGGERS T

  JOIN SYS.ALL_OBJECTS OB ON OB.OBJECT_ID = T.OBJECT_ID

  JOIN SYS.ALL_OBJECTS PB ON PB.OBJECT_ID = OB.PARENT_OBJECT_ID

  JOIN SYS.SCHEMAS SC ON SC.SCHEMA_ID = PB.SCHEMA_ID

  is this will be enough ? or even efficient?[/font]

• Atif-ullah Sheikh

  SSChampion

  Points: 12495

  More actions

  April 22, 2010 at 11:04 pm

  #1156602

  sp_msforeachtable is a better option.

  sp_msforeachDb is another useful procedure.

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

  Sometimes, winning is not an issue but trying.

  You can check my BLOG [font="Arial Black"]here[/font][/url][/right]