sp_MSforeachtable简介； 遍历数据库中的所有表运行命令

介绍 (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.

Microsoft内部始终使用SQL Server中一些未记录的对象，但是有权访问它的任何人都可以使用它们。 这些对象之一是称为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.

sp_MSforeachtable是一个存储过程，通常用于将T-SQL命令迭代地应用于当前数据库中存在的每个表。

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.

由于不存在Microsoft的官方文档，因此大多数人不知道所有这些参数的存在以及如何使用此存储过程对所有数据库表执行简单的操作以外的其他操作。

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.

这是本文的主要目的。 为该未记录SQL Server存储过程提供尽可能多的信息，以便每个人都可以在使用它时最大程度地利用它。

典型用法 (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:
  对数据库中的所有表执行无条件重新索引：

 
  EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'
 

• truncate all tables in the database:
  截断数据库中的所有表：

 
  EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
 

• get the information about the number of records from all tables in the database:
  从数据库的所有表中获取有关记录数的信息：

 
  EXEC sp_MSforeachtable 'SELECT ''?'' TableName, Count(1) NumRecords FROM ?'
 

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存储过程的典型用法示例。 此存储过程允许更多参数。 如果仅传递一个（如上述示例），则默认情况下将使用@ command1参数。

sp_MSforeachtable参数 (sp_MSforeachtable parameters)

So how to kn