On the topic of commands I haven't used much since the last migration
from 7.0 to 2000, while using different methods to test moving data
from 2000 to 2005, I
come across having to deal with constraints and triggers. Lets
say you script out a database structure, indexes, triggers and
constraints and run that in Sql2k5. I have a little tool called
sqlDump that I use to export data without having to create a DTS
package. Now when you want to load up all the data again, you are
going to run into some constraint violations and insert/update triggers
are going to start firing off. Instead of going in to delete all
those constraints and triggers only to add them back later, just
disable them all using a system stored procedure called
sp_MSforeachtable.
sp_MSforeachtable will loop through all the tables in the database,
performing a command. In our case above, we want to run 2
commands: 1 to set NOCHECK on all CONSTRAINTs, and another to DISABLE
all TRIGGERs.
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
The '?' serves as a place holder for the table name. Once your data is loaded, just turn everything back on:
sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
You can be a bit fancier and run more than 1 command per call to
sp_msforeachtable (limited to 3 commands) by passing in parameters of commands you want to
execute per table.
sp_msforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT all", @command2="ALTER TABLE ? ENABLE TRIGGER all"
Another great use for this sp is for DBCC commands on tables or
database, as there is also a sp_MSforeachdb stored procedure too.
sp_msforeachtable "dbcc checktable ('?')"