sp_MSforeachtable

Raymond Lewallen : Using 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 ('?')'"

Cool...

I guess I'll have to rework my procedures that use a cursor against the information_schema.tables view... :|