Vitor Montalvão

An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database

August 18, 2017 by

Introduction

There have always been some undocumented objects in SQL Server that are used internally by Microsoft, but they can be used by anybody that have access to it. One of those objects is a stored procedure called sp_MSforeachtable.

sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database.

Because official documentation from Microsoft doesn’t exist, the majority of people do not know about the existence of all of these parameters and how this stored procedure can be used to perform more than a simple operation over all the database tables.

This is main purpose of this article. To provide as much information as possible for this undocumented SQL Server stored procedure so everybody can take the maximum advantage when use it.

Typical usages

From my experience, I have found in the majority of use cases, people using this stored procedure to:

  • perform an unconditional reindex over all tables in the database:
  • truncate all tables in the database:
  • get the information about the number of records from all tables in the database:

I think by now you should get the idea on how to use this undocumented stored procedure and also realized that the question mark (?) it is used as the replacement of the table and during the execution it will be replaced by the appropriate table name.

But the above are only the examples for the typical usage of the sp_MSforeachtable stored procedure. This stored procedure allows more parameters. If only one is passed (as the above examples) it will use by default the @command1 parameter.

sp_MSforeachtable parameters

So how to know the rest of the parameters if this is an undocumented stored procedure?

We can easily find out the parameters for the sp_MSforeachtable stored procedure by searching for it in the SQL Server Management Studio (SSMS). In SSMS, drill down to “Databases / System Databases / master / Programmability / Stored Procedures / System Stored Procedures” and look for sys.sp_MSforeachtable’s parameters:

@command1, @command2, @command3
sp_MSforeachtable stored procedure requires at least one command to be executed (@command1) but it allows up to 3 commands to be executed. Note that it will start to execute first the @command1 and then @command2 and @command3 by the last and this for each table.

@precommand
Use this parameter to provide a command to be executed before the @command1. It is useful to set variable environments or perform any kind of initialization.

@postcommand
Use this parameter to provide a command to be executed after all the commands being executed successfully. It is useful for control and cleanup processes.

@replacechar
By default, a table is represented by the question mark (?) character. This parameter allows you to change this character.

@whereand
By default, sp_MSforeachtable is applied to all user tables in the database. Use this parameter to filter the tables that you want to work with. On the next section, I will explain how you can filter the tables.

sp_MSforeachtable definition

For a better understanding of the sp_MSforeachtable stored procedure let see it’s code. To get this stored procedure code, in SSMS right-click on the stored procedure name and choose the Modify option.

This will create a new query window with the respective stored procedure code:

You can see now the full code of this undocumented stored procedure. In the header, you can confirm the existence of the parameters referred in the previous section. You can also see that this procedure uses a Cursor (N’declare hCForEachTable cursor global) to go through all user tables (where OBJECTPROPERTY(o.id, N”IsUserTable”) = 1) in the current database. Plus, at the end it concatenates the select statement with whatever we pass in the @whereand parameter. Looking at the code, it joins dbo.sysobjects with sys.all_objects (from dbo.sysobjects o join sys.all_objects syso) so we just need to work from here.

Full example

Now that we have the sp_MSforeachtable stored procedure definition let’s practice with an example so we can try to cover all parameters.

The AdventureWorks database has a HumanResources schema so I am using this database for this example.

Imagine that our Human Resources department requested us to check the database growth because they are facing some kind of disk free space issue and want to determine which table or tables are growing more than expected.

As can be seen in the solution I am presenting below, the @whereand parameter is used to filter only the tables that belong to HumanResources schema. As explained before, the code that is passed in this parameter is concatenated to the SELECT query that will be used by the cursor definition in the sp_MSforeachtable stored procedure so we are using it to filter the HumanResources schema.

I am using the @precommand parameter to create a global temporary table called ##Statistics to store the information that will be need to be shown at the end. This global temporary table will be dropped at the end as it can be seen in the @postcommand parameter after returning the required data (the SELECT statement just before the DROP statement).

So, for each table in that belongs to HumanResources schema it will be applied the code in @command1 and @command2 parameters. As explained before, these parameters are used sequentially, i.e. it will first execute @command1 and after this one finish it will execute @command2. If @command3 parameter exists, it will be executed after @command2 finish. And this is why I have an INSERT statement in @command1 parameter and an UPDATE statement in @command2 parameter, since it is expected that the row already exists when @command2 is executed so we will not insert again the row but update it instead.

Follow is the code that you can test. Feel free to change it to perform other kind of tests that can be useful for you:

For the example above you will get a similar output as below:

NOTE: The solution could be simpler by using the sp_spaceused but I preferred the above solution to better show how to work with more than a command parameter.

Disclaimer

Microsoft does not recommend the usage of the undocumented objects because they do not offer any support for these kinds of objects as they may be changed or dropped in future SQL Server releases without any previous warning.

From my own experience, these undocumented objects have been not experienced any changes in the past so if you want to use them, remember to perform tests with those objects in any new SQL Server version that will be released in the future to assure the correct operation of your code.

See more

For multi-DB script execution, check out ApexSQL Propagate, a free SQL Server tool that can execute multiple SQL scripts on multiple SQL databases, located on any SQL Servers, with one-click.

References:


Vitor Montalvão

Vitor Montalvão

Vitor Montalvão is a senior SQL Server Engineer with more than 20 years of experience working with SQL Server.

He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.

Vitor also has a website with some useful information about SQL Server: https://f1-sqlserver.wixsite.com/f1-sqlserver

View all posts by Vitor Montalvão
Vitor Montalvão
1,312 Views