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.

 

sp_MSforeachtable 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 sp_MSforeachtable procedure in SQL Server

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

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 sp_MSforeachtable 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.