Undocumented Sp_MSforeachtable Procedure in SQL Server

Introduction

All versions of SQL Server have undocumented Stored Procedures or functions. This may be because those Stored Procedures or functions are used by Microsoft internally. This type of Stored Procedure or function (undocumented) can be any without any notification.

The "sp_MSforeachtable" Stored Procedure comes with SQL Server, but it is not documented in MSDN. This Stored Procedure could be found in the Master database. The Stored Procedure "sp_MSforeachtable" allows us to easily process some code against each and every table in a single database. It means that it is used to process a single T-SQL command or number of various T-SQL commands against every table in the database.

Syntax

sp_MSforeachtable [ @command1 = ] 'command1' [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter

Parameter	Description
@command1	It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
@replacechar	It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a "?".
@command2	@command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@command3
@whereand	This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
@precommand	This command is to be run before processing any table. Its data type is nvarchar(2000).
@postcommand	This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).

 

Definition of procedure in SQL Server 2008 R2

 

CREATE PROCEDURE sys.sp_MSforeachtable 

 @command1 NVARCHAR(2000),

 @replacechar NCHAR(1) = N'?',

 @command2 NVARCHAR(2000) = null, 

 @command3 NVARCHAR(2000) = null,

 @whereand NVARCHAR(2000) = null, 

 @precommand NVARCHAR(2000) = null,

 @postcommand NVARCHAR(2000) = null 

AS 

 /* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */ 

 /* @precommand and @postcommand may be used to force a single result set via a temp table. */ 

 

 /* Preprocessor won't replace within quotes so have to use STR(). */ 

 DECLARE @mscat NVARCHAR(12) 

 SELECT @mscat = LTRIM(STR(CONVERT(INT, 0x0002))) 

 

 IF (@precommand is not null) 

  EXEC(@precommand) 

 

 /* Create the SELECT */ 

   EXEC(N'DECLARE hCForEachTable cursor global for SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =

 syso.object_id ' 

         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 ' 

         + @whereand) 

 DECLARE @retval INT 

 SELECT @retval = @@error 

 IF (@retval = 0) 

  EXEC @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 

 

 IF (@retval = 0 and @postcommand is not null) 

  EXEC(@postcommand) 

 RETURN @retval

Example

The following script helps us to list all the tables of the "TestDb" database.

Example script

Use Testdb
exec sp_MSforeachtable 'print "?"'

Output

Another example. The following script helps us to determine the space used and allocated for every table in the database.

Example script

 

Use Testdb
exec sp_MSforeachtable 'EXECUTE sp_spaceused [?];'

Output

Common uses of this Stored Procedure

This stored produce may be used for the following purposes.

• To get the size of all the tables in the database
• To rebuild all indexes of all the tables in the database
• Disable all constraints and triggers of all the tables in the database
• Delete all the data from all the tables in the database
• To RESEED all tables to 0
• To get the Number of Rows in all tables in a database
• Update the statistics of all the tables in a database
• Reclaim space from dropped variable-length columns in tables or indexed views of the database

Conclusion

These undocumented Stored Procedures can be used if we want to do the same operation on each table of any database. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.