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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

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. Required fields are marked *