If youneed to write code that will process a given command against each Microsoft SQL Server database or all tables in a given database, you might consider using a couple of undocumented stored procedures, sp_MSforeachdb and sp_MSforeachtable. Have you been building complicated “cursor while” loops to process through a list of databases or a list of tables? If so, maybe you have been writing too much code. Using the sp_MSforeachdb and sp_MSforeachtable stored procedures (SPs), you can simplify the code you need to process through all databases and/or all tables. Don’t be deceived by the sp_MSforeachtable stored procedure name; this SP can process through other database objects than just tables. This article will discuss how to use these two undocumented stored procedures.

sp_MSforeachdb

The undocumented sp_MSforeachdb SP can be found in the master database. This SP can be used to process a single command like, DBCC CHECKDB against all databases on your system, or can be used to process a set of multiple commands against each database. This SP simplifies the TSQL coding required for a DBA to run a command against each database on their SQL Server machine. Here is the format for calling this procedure:

exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, @command2, 
@command3, @precommand, @postcommand

where the

• @RETURN_VALUE is an integer that is set to the return value associated with SP
• @command1 is a nvarchar(2000) field for specifying the first command to run against each database
• @replacechar is a nchar(1) field that represents the character used in the commands you are executing that will be replaced with the database name prior to being executed
• @command2 is a nvarchar(2000) field for specifying the second command to run against each database
• @command3 is a nvarchar(2000) field for specifying the third command to run against each database
• @precommand is a nvarchar(2000) field for specifying a command to be run prior to processing any commands (@command1, @command2, @command3) against any databases
• @postcommand is a nvarchar(2000) field for specifying a command to be run after all the commands against all database have been processes.

Let’s go through a very simple example of how to call the sp_MSforeachdb SP. This example will simply print the name of each database it processes:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'Print ''The name of the current database is ?'''
exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1

Note how this example executes only a single PRINT statement against each database. In the text being printed, there is a question mark (?), which is the default replace character. When this statement is executed for each database, the question mark will be replaced with the name of the current database being processed. The following would be the typical output if your server only contained the standard Microsoft supplied databases:

The name of the current database is master
The name of the current database is model
The name of the current database is msdb
The name of the current database is Northwind
The name of the current database is pubs
The name of the current database is tempdb

Now, let’s expand on this code by not using the question mark as the replace character. (You might need to do this if the command being executed contains a question mark.) If you do not want to use the question mark as the replace character, use the @replacechar parameter on the execution of the sp_MSforeachdb SP. The following is an example that uses the # symbol as the replace character:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'Print ''What is the name of this database? 
Answer: #'''
exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1, @replacechar='#'

For this example, the output for a server that only contained the standard Microsoft SQL Server supplied databases would look like this:

What is the name of this database? Answer: master
What is the name of this database? Answer: model
What is the name of this database? Answer: msdb
What is the name of this database? Answer: Northwind
What is the name of this database? Answer: pubs
What is the name of this database? Answer: tempdb

You can see that the output now contains a question mark, and the # sign has been replaced by the name of the database for which the command was executed.

These first two examples allow you to see how the calling syntax works and give you a better understanding of how to insert the database name into the command that will be executed against each database. Let’s show an example that takes advantage of the sp_MSforeachdb to perform some real DBA work.

Here is some code that will run a database backup and execute a DBCC CHECKDB command against each database. This code also creates a database backup directory that has a date and time stamp as part of its name and holds all the database backups. In addition, this script also ships all the database backups off to an alternative location for disaster recovery purposes. The following code does all this by using most of the parameters and functionality supported by the sp_MSforeachdb SP:

set nocount on 
declare @RETURN_VALUE int
declare @precommand varchar(1000)
declare @bckup_file char(100)
declare @command1 varchar(1000)
declare @command2 varchar(1000)
declare @postcommand varchar(1000)
set @bckup_file='backup_' +
       replace(replace(convert(char(19),GETDATE(),121),' ','_'),':','')
set @precommand = 'exec master.dbo.xp_cmdshell ''mkdir ' +
                  'c:\temp\' + rtrim(@bckup_file) + ''''
set @command1 = 'Print ''---------------------------------------------'';' +
                'Print ''Processing Database ?'';' +
                'Print ''---------------------------------------------'';' +
                'BACKUP DATABASE [?] TO DISK=''' +
                'c:\temp\' + rtrim(@bckup_file) + '\?.bak'''
set @command2 = 'Print ''---------------------------------------------'';' +
                'Print ''Processing Database ?'';' +
                'Print ''---------------------------------------------'';' +
                'DBCC CHECKDB([?])'
set @postcommand = 'exec master.dbo.xp_cmdshell ''mkdir c:\temp\backup_copy\' +
                   rtrim(@bckup_file) + ''';' +
                   'exec master.dbo.xp_cmdshell ''copy ' +
                   'c:\temp\' + rtrim(@bckup_file) + ' ' +
                   '\\server2\sqlshare\temp\backup_copy\' +
                   rtrim(@bckup_file) + ''''

print @precommand
print @command1
print @command2
print @postcommand
exec @RETURN_VALUE = master.dbo.sp_MSforeachdb
                               @command1 = @command1,--, @replacechar
                               @command2 = @command2, --@command3,
                               @precommand = @precommand,
                               @postcommand = @postcommand
print @RETURN_VALUE

You can see that I used the @precommand parameter to execute xp_cmdshell to run the MKDIR command, which created a directory in which to place all database backups. If this directory had been created with a name like, “c:\temp\ backup_yyyy-mm-dd_hhmmss,” “yyyy” would be the current year, “mm” would be the current month, “dd” would be the current day,“hh” would be the current hour,“mm” would be the current minute, and “ss” would be the current second.

The @command1 variable is used to execute a series of commands. This series consists of three PRINT statements to print a header that displays which database is being backed up, and a BACKUP DATABASE command to back up the current database into the directory created by the @precommand. The @command2 is used to run the DBCC CHECKDB command for each database, as well as to execute a series of PRINT statements to help show which database is being processed by the DBCC command. Lastly, the @postcommand is used to copy the newly created backup directory and all the database backups in that directory to another location — in this case, the “\\server2\sqlshare\temp\backup_copy” directory. Using the @postcommand parameter allows you to create a disaster recovery backup copy on a different server (in this case server2).

As you can see from the previous example, you can process many commands against each database by assigning multiple commands to a single sp_MSforeachdb parameter.

My next example demonstrates how your can perform a lot of work with a single call to the sp_MSforeachdb SP.

sp_MSforeachtable

Because of its name, you would think that the sp_MSforeachtable SP would be used to process a command or series of commands against all tables in a database, right? The SP can indeed do this, but by default, the sp_MSforeachtable SP only processes through the user tables in a database; the system tables are not considered. (Note that by specifying the right calling parameters, the sp_MSforeachtable SP can be used to process through a list of all tables (user and system) and/or any other objects in the sysobjects table.) In this section, I will show you a number of techniques that will allow you to use this SP to process through any subset objects in the sysobjects table.

The sp_MSforeachtable SP can be found in the master database. Using this SP gives the DBA an easy alternative to using a cursor to process a command against each table in a database. Here is the calling syntax for this stored procedure:

exec @RETURN_VALUE=sp_foreachtable @command1, @replacechar, @command2, 
@command3, @whereand, @precommand, @postcommand

Where

• @RETURN_VALUE is an integer that will be set to the return code of the SP
• @command1 is a nvarchar(2000) field used for specifying the first command to be executed
• @replacechar is a char(1) field to be used to identify the character in the command strings that will be replaced with the table name
• @command2 and @command3 fields are nvarchar(2000) fields to specify the second and third commands, respectively, to be executed
• @whereas command is a nvarchar(2000) field used to add additional constraints to further identify which objects in the sysobjects table that this SP will process
• @precommand is a nvarchar(2000) field used to specify a command to be executed prior to processing each of the @command parameters, and
• the last parameter, @postcommand, is a nvarchar(2000) field used to specify a command that will be processed after all the @command’s have been processed against all tables.

The sp_MSforeachtable SP has the same basic calling structure as the sp_MSforeachdb SP, except for the @whereand parameter.

Since the sp_MSforeachtable has a very similar set of parameters as sp_MSforeachdb and the parameters provide the same basic functionality, I will not discuss all these parameters. Instead, I will focus on how you can use the new parameter, @whichand, to control the set of objects in the sysobjects table that will be processed by this SP.

This first example is just a simple example that shows you how to call the sp_MSforeachtable. This example will cause a PRINT command to execute for each table that will echo the name of the table currently being processed. The following example executes the sp_MSforeachtable twice, once for the Northwind database and a second time for the pubs database:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'Print ''The name of current table is ?'''
Print '-----------------------------------------'
Print 'Here are the Northwind tables'
Print '-----------------------------------------'
use Northwind
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1
Print '-----------------------------------------'
Print 'Here are the pubs tables'
Print '-----------------------------------------'
use pubs
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1

Notice that I change the context of the database against which the sp_MSforeachtable will be processed by issuing the USE command. Here is the output that was produced when I ran this code against my Northwind and pubs databases:

-----------------------------------------
Here are the Northwind tables
-----------------------------------------
The name of current table is [dbo].[Orders]
The name of current table is [dbo].[Products]
The name of current table is [dbo].[Order Details]
The name of current table is [dbo].[CustomerCustomerDemo]
The name of current table is [dbo].[CustomerDemographics]
The name of current table is [dbo].[Region]
The name of current table is [dbo].[Territories]
The name of current table is [dbo].[EmployeeTerritories]
The name of current table is [dbo].[Employees]
The name of current table is [dbo].[Categories]
The name of current table is [dbo].[Customers]
The name of current table is [dbo].[Shippers]
The name of current table is [dbo].[Suppliers]
-----------------------------------------
Here are the pubs tables
-----------------------------------------
The name of current table is [dbo].[titleauthor]
The name of current table is [dbo].[stores]
The name of current table is [dbo].[sales]
The name of current table is [dbo].[roysched]
The name of current table is [dbo].[discounts]
The name of current table is [dbo].[jobs]
The name of current table is [dbo].[pub_info]
The name of current table is [dbo].[employee]
The name of current table is [dbo].[authors]
The name of current table is [dbo].[publishers]
The name of current table is [dbo].[titles]

Note that no system tables were returned, only the user tables. This is the default behavior of the sp_MSforeachtable SP. As stated earlier, you can use a calling parameter — @whereand — to override this behavior. This parameter is used to add additional WHERE constraints to the selection criteria for objects in the sysobjects table.

Here is the standard code that is used by this SP to determine what objects are selected from the sysobjects table:

select '[' + REPLACE(user_name(uid), N']', N'] ]') + ']' + '.' + 
'[' + REPLACE(object_name(id), N']', N'] ]') + ']' from 
dbo.sysobjects o  where OBJECTPROPERTY(o.id, N'IsUserTable') = 1  
and o.category & 2 = 0 

This code only selects an object from the sysobjects table if the table is a user table (‘IsUserTable’ = 1). By using the @whereand parameter, you can expand what objects are selected with the above SELECT statement. The @whereand statement is used to add additional WHERE conditions to the WHERE clause in the above SELECT statement. So, if you want to include all tables (user and system), you would set the @whereand parameter to “or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1.” Now, if you set this parameter as stated, your SP call would look like the code below, and when run, it would print all the table in the Northwind database:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
declare @whereand nvarchar(2000)
set @command1 = 'Print ''The name of current table is ?'''
set @whereand = 'or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'
use Northwind
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1,
@whereand = @whereand

So let’s expand on this code by exploiting the @whereand parameter to bring back sysobjects types other than tables. As stated previously, you can use this SP to process through any set of sysobjects objects; all you need to do is set the @whereand parameter appropriately to return only the objects in the sysobjects table that we are interested in processing. So, if you want to process through a list of views, execute the following code:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
declare @whereand nvarchar(2000)
set @command1 = 'Print ''The name of current table is ?'''
set @whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 '  +
                'or OBJECTPROPERTY(o.id, N''IsView'') = 1 ' 
use Northwind
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1, 
@whereand = @whereand

Or, if you only want to return all views and stored procedures, use this code:

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
declare @whereand nvarchar(2000)
set @command1 = 'Print ''The name of current object is ?'''
set @whereand = 'and OBJECTPROPERTY(o.id, N''IsUserTable'') = 0 '  +
                'or OBJECTPROPERTY(o.id, N''IsView'') = 1 ' +
                'or OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
use Northwind
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1, 
@whereand = @whereand

These last few examples should give you a fair understanding of how to use the @whereand parameter to control the objects in the sysobjects table that will be processed by the sp_MSforeachtable SP.

Conclusion

The sp_MSforeachdb and sp_MSforeachtable can do a fair amount of processing for you. Why reinvent the wheel by using a cursor and a “while loop” to process all databases and/or tables within a database? The @whereand parameter can make the sp_MSforeachtable an extremely versatile SP that you can exploit to return whatever subset of object in the sysobjects table that you need to process. If you are currently using cursors and “while loops,” you should consider reviewing how sp_MSforeachdb and SPMSforeachtable store procedures can simplify your TSQL coding.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a website that contains a number of SQL Server examples to common DBA and SQL developer issues.