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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy