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 "?"' 
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 [?];' 
Common uses of sp_MSforeachtable Stored
ProcedureThis 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
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.

HostForLIFE.eu SQL Server 2016 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.