Undocumented stored procedure sp_msforeachtable in SQL Server

Dear All,

Sometimes as DBA’s we need to perform certain action on all the tables in a database. Say, running DBCC Checktable, or disabling all constraints before a bulk load or simply finding the size of each table. There are many ways to do it but one quick way could be using the undocumented stored procedure sp_msforeachtable which can be found in the MASTER database.

For example; running sp_spaceused on all the tables in your database:

USE AdventureWorks
EXECUTE sp_MSforeachtable 'sp_spaceused [?];'

You can see that sp_spaceused stored procedure (which does not require any explanation) runs on all the tables.

1_Undocumented_stored_procedure_sp_msforeachtable_in_SQL_Server

   

This undocumented stored procedure can be used in many interesting ways and in my future posts, I shall show a few things.

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

6 Comments on “Undocumented stored procedure sp_msforeachtable in SQL Server”

  1. Hmmm…

    Thats one undcoumented SP that I will miss if decides to Microsoft deprecates…..That will be just foolish.

    Just imagine doing this(Having NoCheck on all constraint for the tables in a DB) with simple TSQL..

    EXEC

    sp_msforeachtable @command1 = “ALTER TABLE ? NOCHECK CONSTRAINT all”

    ;

Leave a Reply

Your email address will not be published.