Raymond Lewallen

Sponsors

The Lounge

Advertisement

Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
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 ('?')"

Posted Fri, Mar 25 2005 4:34 AM by Raymond Lewallen
Filed under:

[Advertisement]

Comments

Jordan wrote re: Using sp_MSforeachtable
on Sun, Mar 27 2005 8:14 AM
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.
Jordan wrote re: Using sp_MSforeachtable
on Sun, Mar 27 2005 11:53 AM
And by Robert, I of course mean Raymond. Sorry 'bout that. :o)
Raymond Lewallen wrote re: Using sp_MSforeachtable
on Mon, Mar 28 2005 5:22 AM
Jordan, find the software at http://sqldump.sourceforge.net/
K. Scott Allen wrote Care and Feeding Of Community Server
on Mon, Feb 6 2006 10:21 PM
Over the weekend, OdeToCode bumped up against it’s SQL Server disk space quota. Some operations, like...
VirtualSpy’s Answers » Blog Archive » Perform a SQL task on every table using sp_MSforeachtable wrote VirtualSpy’s Answers » Blog Archive » Perform a SQL task on every table using sp_MSforeachtable
on Sat, Dec 27 2008 5:51 PM

Pingback from  VirtualSpy’s Answers  » Blog Archive   » Perform a SQL task on every table using sp_MSforeachtable

ray wrote re: Using sp_MSforeachtable
on Mon, Aug 31 2009 1:30 PM

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...'

Add a Comment

(required)  
(optional)
(required)  
Remember Me?