An introduction to sp MSforeachtable run commands iteratively through all tables in a database
An introduction to sp_MSforeachtable run commands iteratively through all tables in a database
He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.
Vitor also has a website with some useful information about SQL Server: https://f1-sqlserver.wixsite.com/f1-sqlserver
View all posts by Vitor Montalvão Latest posts by Vitor Montalvão (see all) An introduction to sp_MSforeachtable run commands iteratively through all tables in a database - August 18, 2017 SQL Server system databases – the model database - August 9, 2017 SQL Server system databases – the msdb database - July 14, 2017
SQLShack
SQL Server training EspañolAn introduction to sp_MSforeachtable run commands iteratively through all tables in a database
August 18, 2017 by Vitor MontalvãoIntroduction
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. 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. 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. 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.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: 123 EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')' truncate all tables in the database: 123 EXEC sp_MSforeachtable 'TRUNCATE TABLE ?' get the information about the number of records from all tables in the database: 123 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 parameters
So how to know the rest of the parameters if this is an undocumented stored procedure? We can easily find out the parameters for the sp_MSforeachtable stored procedure by searching for it in the SQL Server Management Studio (SSMS). In SSMS, drill down to “Databases / System Databases / master / Programmability / Stored Procedures / System Stored Procedures” and look for sys.sp_MSforeachtable’s parameters: @command1, @command2, @command3 sp_MSforeachtable stored procedure requires at least one command to be executed (@command1) but it allows up to 3 commands to be executed. Note that it will start to execute first the @command1 and then @command2 and @command3 by the last and this for each table. @precommand Use this parameter to provide a command to be executed before the @command1. It is useful to set variable environments or perform any kind of initialization. @postcommand Use this parameter to provide a command to be executed after all the commands being executed successfully. It is useful for control and cleanup processes. @replacechar By default, a table is represented by the question mark (?) character. This parameter allows you to change this character. @whereand By default, sp_MSforeachtable is applied to all user tables in the database. Use this parameter to filter the tables that you want to work with. On the next section, I will explain how you can filter the tables.sp_MSforeachtable definition
For a better understanding of the sp_MSforeachtable stored procedure let see it’s code. To get this stored procedure code, in SSMS right-click on the stored procedure name and choose the Modify option. This will create a new query window with the respective stored procedure code: 12345678910111213141516171819202122232425262728293031323334353637383940414243 USE [master]GO/****** Object: StoredProcedure [sys].[sp_MSforeachtable] Script Date: 8/18/2017 8:47:44 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGO ALTER proc [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) = nullas /* 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 You can see now the full code of this undocumented stored procedure. In the header, you can confirm the existence of the parameters referred in the previous section. You can also see that this procedure uses a Cursor (N’declare hCForEachTable cursor global) to go through all user tables (where OBJECTPROPERTY(o.id, N”IsUserTable”) = 1) in the current database. Plus, at the end it concatenates the select statement with whatever we pass in the @whereand parameter. Looking at the code, it joins dbo.sysobjects with sys.all_objects (from dbo.sysobjects o join sys.all_objects syso) so we just need to work from here.Full example
Now that we have the sp_MSforeachtable stored procedure definition let’s practice with an example so we can try to cover all parameters. The AdventureWorks database has a HumanResources schema so I am using this database for this example. Imagine that our Human Resources department requested us to check the database growth because they are facing some kind of disk free space issue and want to determine which table or tables are growing more than expected. As can be seen in the solution I am presenting below, the @whereand parameter is used to filter only the tables that belong to HumanResources schema. As explained before, the code that is passed in this parameter is concatenated to the SELECT query that will be used by the cursor definition in the sp_MSforeachtable stored procedure so we are using it to filter the HumanResources schema. I am using the @precommand parameter to create a global temporary table called ##Statistics to store the information that will be need to be shown at the end. This global temporary table will be dropped at the end as it can be seen in the @postcommand parameter after returning the required data (the SELECT statement just before the DROP statement). So, for each table in that belongs to HumanResources schema it will be applied the code in @command1 and @command2 parameters. As explained before, these parameters are used sequentially, i.e. it will first execute @command1 and after this one finish it will execute @command2. If @command3 parameter exists, it will be executed after @command2 finish. And this is why I have an INSERT statement in @command1 parameter and an UPDATE statement in @command2 parameter, since it is expected that the row already exists when @command2 is executed so we will not insert again the row but update it instead. Follow is the code that you can test. Feel free to change it to perform other kind of tests that can be useful for you: 1234567891011121314151617181920212223242526 USE AdventureworksGO exec sp_MSforeachtable @precommand = 'CREATE TABLE ##Statistics (TableName varchar(128) NOT NULL, NumOfRows int, SpaceUsed float)',@command1='INSERT INTO ##Statistics (TableName, NumOfRows) SELECT ''?'' TableName, COUNT(1) NumOfRows FROM ?',@command2='UPDATE S SET s.SpaceUsed = g.SizeKB FROM ##Statistics s INNER JOIN (SELECT p.object_id TableID, sum(a.total_pages) * 8192 / 1024.0 SizeKB FROM sys.partitions p INNER JOIN sys.allocation_units a on p.partition_id = a.container_id GROUP BY p.object_id) g ON OBJECT_ID(s.TableName) = g.TableID WHERE s.TableName = ''?''',@postcommand = 'SELECT TableName, NumOfRows, SpaceUsed FROM ##Statistics ORDER BY SpaceUsed DESC, NumOfRows DESC; DROP TABLE ##Statistics',@whereand='AND schema_name(schema_id) = ''HumanResources''' For the example above you will get a similar output as below: NOTE: The solution could be simpler by using the sp_spaceused but I preferred the above solution to better show how to work with more than a command parameter.Disclaimer
Microsoft does not recommend the usage of the undocumented objects because they do not offer any support for these kinds of objects as they may be changed or dropped in future SQL Server releases without any previous warning. From my own experience, these undocumented objects have been not experienced any changes in the past so if you want to use them, remember to perform tests with those objects in any new SQL Server version that will be released in the future to assure the correct operation of your code. Author Recent Posts Vitor MontalvãoVitor Montalvão is a senior SQL Server Engineer with more than 20 years of experience working with SQL Server.He participates in some SQL Server forums, helping other professionals solving SQL Server issues and acting as their mentor whenever is possible.
Vitor also has a website with some useful information about SQL Server: https://f1-sqlserver.wixsite.com/f1-sqlserver
View all posts by Vitor Montalvão Latest posts by Vitor Montalvão (see all) An introduction to sp_MSforeachtable run commands iteratively through all tables in a database - August 18, 2017 SQL Server system databases – the model database - August 9, 2017 SQL Server system databases – the msdb database - July 14, 2017