Using sp_MSforeachtable

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 (‘?’)”

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

4 Responses to Using sp_MSforeachtable

  1. ray says:

    hi raymond

    if i want to truncate the table in one of my database how do i run this sp.msforeachtable?

    i tried it and i get ‘incorrect syntax near…’

  2. Jordan says:

    And by Robert, I of course mean Raymond. Sorry ’bout that. :o)

  3. Jordan says:

    Hi Robert,

    The tool sqlDump you mentioned cought my eye. Is this something that you put together, or is it a script that lives out there somewhere on the net? A 3rd party app perhaps?

    Anyhow, I find myself writing a quick sync tool to blast big tables up to a central server and have elected to dump to a file and fire it up to the server over FTP (or .net remoting if the overhead isn’t too great). Avoiding DTS packages would be great. Can you point me in the direction of the sqlDump tool if it’s available?

    Thanks much!

    Jordan.

Leave a Reply