[Translation] SQL Server has not published two stored procedures: sp_MSforeachtable and sp_MSforeachdb

SQL Server does not disclose two stored procedures: sp_MSforeachtable and sp_MSforeachdb

Have you ever written code to process all tables in the database? How can I write code to process all databases in an SQL Server instance? However, do you know there are many ways to solve this problem? You can createCursorCursor contains all data tables, or all databases that contain the SQL Server instance, or uses a non-public (untitled ented)Stored Procedure. This article will explain how stored procedures work and how Application Instances can be used. Non-public stored procedures are easier to use than cursors.

Overview

Microsoft provides two non-public stored procedures, allowing you to iteratively process all tables in the database or all databases in the SQL Server instance. The first stored procedure is "sp_MSforeachtable", which allows you to easily use code to process all tables in the database; the other is "sp_MSforeachdb", which processes all databases in the SQL Server instance. Let's have a deep understanding of these two stored procedures.

Sp_MSforeachtable

"Sp_MSforeachtable" does not appear in the online document. It exists in the master database and can execute one or more T-SQL commands on all tables in the given database. See the following example.

Assume that you need to create a temporary table to record the table name and number of Row Records of the current database. To implement this function, you need to execute the following command: "select '<mytable>', count (*) from <mytable> ". Replace "<mytable>" with the name of each table in the database and insert the result to the temporary table. We will use the cursor and the non-public "sp_MSforeachtable" to implement them respectively.

Use a cursor:

The output result is as follows:

The following code uses the Non-Public "sp_MSforeachtable" to generate the same result:

The result is as follows:

It can be seen that using a cursor and sp_MSforeachtable can generate the same result. Which method do you think is more readable and easier? The syntax of sp_MSforeachtable is described as follows:

Exec @ RETURN_VALUE = sp_MSforeachtable @ command1, @ replacechar, @ command2,

@ Command3, @ whereand, @ precommand, @ postcommand

Note:

• @ RETURN_VALUE-Return Value
  
• @ Command1-the type is nvarchar (2000), and sp_MSforeachtable is the first command to be executed.
  
• @ Replacechar-during the processing, replace the command line character with the specific table name (default value "? ")
  
• @ Command2 \ @ command3: execute these two commands for each data table. @ command2 is executed after @ command1 and @ command3 is executed after @ command2.
  
• @ Whereand-the type is varchar (2000). It provides additional constraints to filter rows in the sysobjects table.
  
• @ Precommand-the type is varchar (2000). Run this command before processing any table.
  
• @ Postcommand-the type is varchar (2000). Run this command after processing all tables.
  

The following examples demonstrate how to use this stored procedure to process all or some tables.

The following code queries a table starting with the letter p and sets the filter condition with the parameter @ whereand:

The result is as follows:

The above Code uses the parameters @ command1 and @ whereand. The parameter @ whereand is used to set the WHERE condition to filter out the table names starting with the letter p. I set the parameter value to "and o. name like ''p % ''". If you want to use multiple condition constraints, such as starting with p or starting with a, set the parameter value:

And o. name like ''p % ''or o. name like ''a %''

 

If the statement has a problem, remove the name prefix as follows:

 

And name like ''p % ''or name like ''a %''

 

Note: The parameter @ command1 in the above example uses "? ", Which is called the replacement character (replacement character). By default, it is replaced by all table names. If you want to use "? "As the content rather than the replacement character replaced by the table name, you can use the @ replacechar parameter to set the replacement character. The following example uses "{" as the replacement character.

The result is as follows:

There are also two parameters @ precommand and @ postcommand. Let's take a look at the example below and integrate all the statements in the example above into a simple stored procedure call.

Note that the global temporary table # rowcount is used in the preceding example. If the temporary table # rowcount is used, an error is returned. The @ precommand parameter creates a global temporary table. It is executed only once and prior to the @ command1 statement. @ Postcommmand: the statement is executed only once after all tables are processed iteratively. It is used to display the result and delete the temporary table.

 

Sp_MSforeachdb

Sp_MSforeachdb is also in the master database, which iterates each database in the SQL Server instance to execute T-SQL statements, such as "DBCCCHECKDB", while looking at its syntax

Exec @ RETURN_VALUE = sp_MSforeachdb @ command1, @ replacechar,

@ Command2, @ command3, @ precommand, @ postcommand

Note:

• @ RETURN_VALUE-Return Value
  
• @ Command1-the type is nvarchar (2000), the first command to be executed
  
• @ Replacechar-replace character. The command string is replaced with the actual Database Name (default value "? ")
  
• @ Command2 \ @ command3: execute these two commands for each database. @ command2 is executed after @ command1 and @ command3 is executed after @ command2.
  
• @ Precommand-the type is varchar (2000). Run this command before processing any database.
  
• @ Postcommand-the type is varchar (2000). Run this command after processing all databases.
  

The parameters of sp_MSforeachdb are similar to those of sp_MSforeachtable.

See the following simple example. This example backs up the database and then performs "dbcc checkdb" for each database ":

Here I use three different commands. The first one prints the name of the database being processed. Sp_MSforeachtable has a parameter used to filter data tables to be processed, but sp_MSforeachdb does not have a similar filter parameter. Since SQL Server does not support backup of tempdb, I want to skip tempdb, which is why I use IF in each command. The second command backs up the database, and the last command runs "dbcc checkdb" on databases other than tempdb ".

Before running the preceding command, you must first create the directory "c: \ temp". Below are some output results:

 

SQL Server non-public stored procedures

When using these non-public stored procedures, you must be careful and perform tests. Because it is not made public, Microsoft may modify any version or patch package without notice. Therefore, you need to perform a comprehensive test on all SQL Server versions to verify that your code is still running normally in the new version.

Conclusion

As you can see, these two non-public stored procedures are easier to use than cursors, and later you can use them to iterate over data tables or databases. However, keep in mind that these two stored procedures are non-public and Microsoft may change their functions at any time.

Reference

SQL Server unmarshented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

Sp_MSforeachtable