solving sql server problems for millions of dbas and developers since 2006

Top SQL Server Tools

SQL compliance manager

SQL safe backup

SQL diagnostic manager

SQL Monitor

SQL Sentry Performance Advisor for SQL Server

Iterate through SQL Server database objects without cursors

Problem
There are times when you need to loop through all the databases or database objects to perform some tasks. For example you want to run a DBCC command against all the databases or take backups of all the databases on the server or you want to rebuild all the indexes of all the tables in the databases or you want to know the size of each table in a database. The simplest approach would be to create a cursor and loop through it, which requires you to write several lines of code. Is there any way to simplify the coding efforts for these kind of works?

Solution
SQL Server has couple of undocumented system stored procedures, in the master database, which allow you to loop through all/selected databases using sp_MSforeachdb system stored procedure or loop through all/selected user tables using sp_MSforeachtable system stored procedure. You can even extend the functionality for views, stored procedures etc by using the sp_MSforeach_worker stored procedure, which is in fact used by the above two stored procedures as well.

These two system stored procedures use almost the similar set of parameters (more details in the below table) and return an integer value.

Parameters	Type	sp_Msforeachtable	sp_Msforeachdb	Description
@precommand	nvarchar(2000)	Yes	Yes	This command is executed before any commands and can be used for setting up an environment for commands execution.
@command1	nvarchar(2000)	Yes	Yes	First command to be executed against each table/database.
@command2	nvarchar(2000)	Yes	Yes	Second command to be executed against each table/database.
@command3	nvarchar(2000)	Yes	Yes	Third command to be executed against each table/database.
@postcommand	nvarchar(2000)	Yes	Yes	This command is executed after any other commands and can be used for cleanup process after commands execution.
@replacechar	nchar(1)	Yes	Yes	Default value is "?" which represents the database/table name. You may need to change this value if you want "?" mark to be used in your query.
@whereand	nvarchar(2000)	Yes	No	With this you can specify the filtering criteria for your table collection. For details see the script section,

Script #1 contains a simple script to demonstrate the use of sp_MSForEachTable stored procedure. The first script lists all the tables and total number of rows in the current database whereas the second script displays the space used by each table in the current database.

Script #1 : sp_MSForEachTable system stored procedure

--List all the tables of current database and total no rows in it
EXEC sp_MSForEachTable 'SELECT ''?'' as TableName, COUNT(1)
as TotalRows FROM ? WITH(NOLOCK)'

--List all the tables of current database and space used by it EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
GO

Script #2 extends the usage of last script to use other parameters. This script creates a temporary table to hold the resultsets returned by the sp_spaceused stored procedure in the pre-execute phase. Then with @command1 it updates the statistics for all the tables and with @command2 it inserts the results to the temporary table created in the pre-execute phase. Further it narrows down the list of tables to consider, which is only tables belonging to HumanResources schema by using the @whereand parameter. Finally after execution of all these commands (during post execution) it selects records from the temporary table and then drops it.

Script #2 : sp_MSForEachTable system stored procedure

--Creates a temporary table to hold the resultsets
--returned by sp_spaceused and before calling it,
--it updates the statistics for each table
--Filter out tables of HumanResources schema only
EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)',
@command1 = 'UPDATE STATISTICS ?;',
@command2 = 'INSERT INTO ##Results EXECUTE sp_spaceused [?];',
@whereand = 'and schema_name(schema_id) = ''HumanResources''',
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go

By default sp_MSForEachTable internally uses OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 to consider only user tables. You can change this default behavior by using @whereand parameter to consider system tables or views or stored procedures or combination of these etc. For example in Script #3, the first script uses the last script as above and considers both user tables as well as system tables. In the second script it considers only views and displays its text likewise in last script it considers only stored procedures and displays its text.

Script #3 : sp_MSForEachTable system stored procedure

--Creates a temporary table to hold the resultsets
--returned by sp_spaceused and before calling it,
--it updates the statistics for each table
--Note it consider both user and system tables
EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results
( name nvarchar(128),
rows char(11),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)',
@command1 = 'UPDATE STATISTICS ?;',
@command2 = 'INSERT INTO ##Results EXECUTE sp_spaceused [?];',
@whereand = 'or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1',
@postcommand = 'SELECT * FROM ##Results; DROP TABLE ##Results'
Go

Use AdventureWorks
GO
--Display the views' script text
EXECUTE sp_MSforeachtable
@command1 = 'sp_helptext [?];',
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0
or OBJECTPROPERTY(o.id, N''IsView'') = 1'
Go

Use AdventureWorks
GO
--Display the stored procedures' script text
EXECUTE sp_MSforeachtable
@command1 = 'sp_helptext [?];',
@whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0
or OBJECTPROPERTY(o.id, N''IsProcedure'') = 1'
Go

Script #4 demonstrate the usage of sp_MSForEachDb stored procedure. The first script runs DBCC CHECKDB command against all the database to check the allocation, logical and physical structural integrity of all the objects inside a database. The second script first excludes the system databases from the consideration and takes a backup of all the user databases.

Script #4 : sp_MSForEachDb system stored procedure

--Checks the allocation, logical and physical structural
--integrity of all the objects of all the databases
EXEC sp_MSForEachdb
@command1 = 'DBCC CHECKDB([?])'
GO

--Does Backup of all the databases except system databases
DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'')' + 'BEGIN '
+ 'Print ''Backing up ? database...'';'
+ 'BACKUP DATABASE [?] TO DISK=''' + 'D:\?_' + replace(convert(varchar,GETDATE(),120),':','') + '.bak'''
+ 'END'
EXEC sp_MSForEachdb
@command1 = @cmd1
GO

Next Steps

The undocumented system stored procedures may change without any notification, so take this into consideration and plan accordingly.
Review all my published tips
Review Run The Same SQL Command Against All SQL Server Databases tip.

Related Tips: 1 | 2 | 3 | More | Become a paid author

Last Update: 12/21/2009

Share:

Free SQL Server Learning

Comments and Feedback:

Monday, December 21, 2009 - 11:57:41 AM - DavidB

Read The Tip

The sample scripts will be very useful. It is clever how you excluded the system databases.

Can you provide an example using sp_MSforeach_worker? This stored procedure was unknown to me until now.

Thanks for sharing your expertise.

Tuesday, December 22, 2009 - 2:02:43 AM - arshad0384

Read The Tip

Hi DavidB,

Thanks for your appreciation, I am glad you liked it. :)

For example on sp_MSforeach_worker, please refer to the definition of either sp_MSforeachtable or sp_MSForEachdb system stored procedure in master database. It gives a very good understanding of how it works. And also you can see the definition of sp_MSforeach_worker system stored procedure there itself.

Wednesday, December 07, 2011 - 9:24:39 AM - Sean Lange

Read The Tip

Nice article about these two very useful stored procs. I would however disagree that this approach is not using a cursor. The two MS stored procs are based solely on a cursor. They use a cursor with dynamic sql. Cursors have kind of become a poison in the sql server world, and rightly so. They are pretty horrible for performance. There are however times when a cursor is pretty much the only way to accomplish something. Executing a script against every database is one such task.

Wednesday, December 07, 2011 - 10:43:12 AM - John Henderson

Read The Tip

Sean, you beat me to the punch, I was just getting ready to point out that very fact.

Arshad, would it be possible for you to update the title of this tip, as not to unknowningly mislead people who are not familiar with the undocumented stored procs. The procs are very handy when you need them, but people should understand that they are still using cursors, and the side-effect of doing so.

Perhaps the title could be "Iterate through SQL Server database objects without writing cursors"

Useful article, nonetheless, to inform people of these hidden gems.

Wednesday, December 07, 2011 - 12:21:48 PM - Marcia Q	Read The Tip
I've been using these sp's for quite a while without realizing there were multiple param options. I've only ever written a basic command. Nice to learn more about it. Thanks for the article.

Sponsor Information

"SQL diagnostic manager delivers response in minutes, not hours!"

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Get SQL Server Tips Straight from Kevin Kline.

Join the over million SQL Server Professionals who get their issues resolved daily.

Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts