Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for May 7th, 2007

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”
sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”


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.

Posted in SQL Server | 1 Comment »