Following my first article that explained this by using a cursor, there is also an undocumented stored procedure to execute the same operation against every table in a database.
Check out this script to disable all constraints and triggers with only 2 lines of code:
| exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all' |
This could be interesting to truncate all tables are before you do a bulk insert.
Afterwards just enable them with another 2 lines of code:
| exec sp_msforeachtable @command1='print "?"', @command2='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' exec sp_msforeachtable @command1='print "?"', @command2='ALTER TABLE ? ENABLE TRIGGER ALL' |
Yep, that's it. Only 2 lines of code!