Systems Engineering and RDBMS

sp_msforeachtable and sp_msforeachdb

Posted by decipherinfosys on May 7, 2007

These are some of the un-documented system stored procedures in SQL Server.

  • sp_msforeachtable: As the name suggests, it can be used to execute one single command against all the tables in the schema.
  • sp_msforeachdb: As the name suggests, this can be used to execute the same T-SQL command against all the databases on a given instance.

These procedures can come in very handy for doing DB admin work and at times during the data loads.  Work that would be done in several lines of code could be accomplished via a simple one line command.  Some examples for the usage of these procedures are:

/*Checking and enabling all the constraints and triggers – likewise, you can disable them first – this is handy at the time of data loads into the system*/

sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? CHECK CONSTRAINT all”
go
sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”

go

And once the data load is finished, you can run DBCC CHECKCONSTRAINTS to see which ones had failures as per the RI in the system. DBAzine has a very good post on this topic – it covers the different parameter options for these procedures as well as sample executions of the same – here.  Please do remember that these are un-documented system stored procedures so these are not supported by MSFT.  So, while usage of these is fine for some admin work, we would not recommend using these in your actual development code.

One Response to “sp_msforeachtable and sp_msforeachdb”

  1. […] by decipherinfosys on June 8th, 2007 In one of our previous blog post, we had covered two un-documented but very handy system stored procedures in SQL Server. One of […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: