Iterate through SQL Server database objects without cursors

By: Arshad Ali   |   Updated: 2009-12-21   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Scripts

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 a couple of undocumented system stored procedures in the master database, which allow you to loop through all or selected databases using the sp_MSforeachdb system stored procedure or loop through all or selected user tables using the 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 same set of parameters (more details in the below table) and return an integer value.

Script #1 using sp_MSForEachTable

This 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 using sp_MSForEachTable

This 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 

Script #3 using sp_MSForEachTable

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 using sp_MSForEachDB

Script #4 demonstrates the usage of the 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 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 Run The Same SQL Command Against All SQL Server Databases tip.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips