Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disable / Enable All triggers on all tables in sql server 2005 Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 7:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 28, 2012 2:15 AM
Points: 1, Visits: 90
Comments posted to this topic are about the item Disable / Enable All triggers on all tables in sql server 2005
Post #903710
Posted Sunday, April 18, 2010 11:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 6:19 PM
Points: 3, Visits: 45
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.
Post #905705
Posted Sunday, April 18, 2010 11:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 28, 2012 2:15 AM
Points: 1, Visits: 90
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.
Post #905707
Posted Monday, April 19, 2010 1:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 11:00 PM
Points: 211, Visits: 1,113
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?
Post #905732
Posted Thursday, April 22, 2010 11:04 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
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 here

Post #909186
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse