sp_MSforeachtable

What is sp_MSforeachtable?

The stored procedure sp_MSforeachtable is one of the system procedures defined in SQL Server. You can find it when you connect to a server with Management Studio or Data Studio and then look under “System Databases” in the “master” database under “Programmability” in the “System Stored Procedures”. There you will find a whole bunch of procedures. Some of them are not officially documented, like the sp_MSforeachtable, which you will now get to know. You shouldn’t use it in a productive environment, but it can save you a lot of work in development. So let’s take a look at it.

How can you use sp_MSforeachtable?

To iterate over the tables in your database and execute a command for each table, you can write your own queries that find your tables in the sys tables, iterate them and generate dynamic SQL statements that you want to execute. Or you can rely on the preliminary work that Microsoft has done for you at this point and use the sp_MSforeachtable.

This procedure has the following arguments:

  • @command1: the first SQL command you want to execute for each table
  • @replacechar: the symbol you want to replace with the respective table name in the query. The default is the question mark, if you do not pass this parameter, the question mark will be replaced by the table name in your query.
  • @command2 and @command3: additional SQL commands you want to execute for each table
  • @whereand: a WHERE condition that selects the tables you iterate over.
  • @precommand and @postcommand: commands you want to execute before or after iterating over the tables.

Many of the parameters become clearer as you start using the procedure, so let’s start with a simple example on the WideWorldImporters database. The following command will give you all the tables in the database:

exec sp_MSforeachtable @command1='print ''?'''

Now let’s take care of the where-condition to filter the tables. With the following query you can only display the tables from the application schema:

exec sp_MSforeachtable 
     @command1='print ''?'''
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''

I

You will see later that the sp_MSforeachtable will join the sysobjects table with the sys.all_objects table, so you can use all the attributes of these tables to write your where condition. If you want to go deeper into using the `sp_MSforeachtable’ procedure than you would in a ninja post, you should definitely take another look at these tables.

However, let’s go one step further and work with the tables we filtered here. Now, contrary to all best practices, we create a table in the dbo schema called tables, in which we enter the table names:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'

Now we can query dbo.tables:

SELECT fullname 
FROM dbo.tables

But of course we can also clear the table right after we filled it, for that we use the @postcommand:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'
    ,@postcommand = 'SELECT fullname FROM dbo.tables; DROP TABLE dbo.tables'

As a result we see again the table names in our select, but if we now want to query the dbo.tables table after the procedure is finished, we find that it no longer exists:

SELECT fullname 
FROM dbo.tables

It was in fact cleaned up by the @postcommand as expected.

Now, completely unconventionally and not at all recommended in productive environments, we want to re-index all these tables. For this we can use the second command, which we can pass to sp_MSforeachtable:

exec sp_MSforeachtable 
     @command1='INSERT INTO dbo.tables (fullname) VALUES (''?'')'
    ,@command2='DBCC DBREINDEX(''?'')'
    ,@whereand = 'AND schema_name(schema_id) = ''Application'''
    ,@precommand = 'DROP TABLE IF EXISTS dbo.tables; CREATE TABLE dbo.tables (fullname varchar(500))'
    ,@postcommand = 'SELECT fullname FROM dbo.tables; DROP TABLE dbo.tables'

Since the procedure itself is not documented, there is of course no official documentation. How can you find out what exactly will happen when you call the procedure? Simple: find the procedure in the Management Studio or Azure Data Studio, right click on it and then click on “Script as create”, then you will see the source code of the procedure and can analyze it for yourself, take it apart and understand how the parameters work. So to speak, Microsoft provides you with a Dojo for the use of system tables for free. So Katana will be pulled out and you will face the enemy.

Further reading:

Ninja-Notebooks @ GitHub

Leave a Reply

Your email address will not be published.