April 14, 2010 at 7:43 pm
Comments posted to this topic are about the item Disable / Enable All triggers on all tables in sql server 2005
April 18, 2010 at 11:50 pm
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.
April 18, 2010 at 11:59 pm
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.
April 19, 2010 at 1:06 am
[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]
April 22, 2010 at 11:04 pm
sp_msforeachtable is a better option.
sp_msforeachDb is another useful procedure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply