SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

Have you ever needed to
write some code that would process through all the tables in a database?
What about running some code against each database on a SQL Server instance? If so, did
you know there are a couple of different ways to accomplish this? Either you
can create a cursor that contains all the tables in a database, or all the databases
on a SQL Server instance, or you can use a couple of undocumented SQL
Server Stored Procedures. This article will show you how the undocumented
Stored Procedures work, and will provide you with some examples on how to use
them. This author thinks the undocumented Stored Procedures are much easier to
use than using a cursor. Read on and decide for yourself which method you
think is easier.

Overview

Microsoft provides two
undocumented Stored Procedures that allow you to process through all tables in
a database, or all databases in a SQL Server instance. The first Stored
Procedure (SP), “sp_MSforeachtable,” allows you to easily process some
code against every table in a single database. The other SP, “sp_MSforeachdb,”
will execute a T-SQL statement against every database associated with the current SQL
Server instance. Let me go through each of these Stored Procedures in a little
more detail.

sp_MSforeachtable

The “sp_MSforeachtable” SP
comes with SQL Server, but it is not documented in Books Online. This SP can
be found in the “master” database and is used to process a single T-SQL command
or a number of different T-SQL commands against every table in a given
database. To demonstrate how this SP works and how I think it is simpler
to use then coding a CURSOR let me go through an example.

Say I want to build a temporary
table that will contain a series of records; one for each table in the database
and where each row contains the table name and the row count for the given table.
To do this you would want to run a command like, “select ‘<mytable>’, count(*)
from <mytable>” where “<mytable>” was replaced with every table in
your database and insert the results into my temporary table. So now,
let’s look at how we might do this using a CURSOR and then using the
undocumented SP “sp_MSforeachtable”.

Here is my code for getting
the row counts for each table in a database using a CURSOR:


use pubs
go
set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = ‘base table’
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
set @cmd = ‘select ”’ + @table + ”’, count(*) from ‘ + @table
insert into #rowcount exec (@cmd)
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
select top 5 * from #rowcount
order by tablename
drop table #rowcount

Here is the output of my
CURSOR example when the above code in run on my machine:


tablename rowcnt
————- ———–
authors 23
discounts 3
employee 43
jobs 8
pub_info 8

Now here is my code that
produces similar results using the undocumented SP “sp_MSforeachtable”:


use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
‘insert into #rowcount select ”?”, count(*) from ?’
select top 5 * from #rowcount
order by tablename
drop table #rowcount

Here is the output from the
above code when run on my machine:


tablename rowcnt
—————– ———–
[dbo].[authors] 23
[dbo].[discounts 3
[dbo].[employee] 43
[dbo].[jobs] 14
[dbo].[pub_info] 8

As you can see both the CURSOR
example and the “sp_MSforeachtable” code produce relatively the same results.
Which one do you think is easier to read and code? Let’s look a little closer
at how to use the undocumented SP “sp_MSforeachtable”.

Below is the syntax for
calling the sp_MSforeachtable SP:


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

Where:

  • @RETURN_VALUE – is the return
    value which will be set by “sp_MSforeachtable”

  • @command1 – is the first command
    to be executed by “sp_MSforeachtable” and is defined as a nvarchar(2000)

  • @replacechar – is a character in
    the command string that will be replaced with the table name being processed
    (default replacechar is a “?”)

  • @command2 and @command3 are two
    additional commands that can be run for each table, where @command2 runs after
    @command1, and @command3 will be run after @command2

  • @whereand – this parameter can be
    used to add additional constraints to help identify the rows in the sysobjects
    table that will be selected, this parameter is also a nvarchar(2000)

  • @precommand – is a nvarchar(2000)
    parameter that specifies a command to be run prior to processing any table

  • @postcommand – is also a nvarchar(2000)
    field used to identify a command to be run after all commands have been
    processed against all tables

As you can see, there are
quite a few options for the “sp_MSforeachtable” SP. Let’s go through a
couple of different examples to explore how this SP can be used to process
commands against all the tables, or only a select set of tables in a
database.

First let’s build on our
original example above and return row counts for tables that have a name that start
with a “p.” To do this we are going to use the @whereand parameter. Here is
the code for this example:


use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = ‘insert into #rowcount select ”?”,
count(*) from ?’,
@whereand = ‘and name like ”p%”’
select top 5 * from #rowcount
order by tablename
drop table #rowcount

On my machine, the above
code produced the following output:


tablename rowcnt
—————— ———–
[dbo].[pub_info] 8
[dbo].[publishers] 8

By reviewing the code above,
you can see I am now using the @command1, and the @whereand parameter. The @whereand
parameter above was used to constrain the WHERE clause and only select tables that
have a table name that starts with a “p.” To do this I specified “and name like ''p%''” for the @whereand parameter. If you needed to
have multiple constraints like all tables that start with “p,” and all
the tables that start with “a,” then the @whereand parameter would look like
this:

and name like ''p%'' or name like ''a%''

Note, that in the @command1
string in the above example there is a “?”. This “?” is the default
replacement character for the table name. Now if for some reason you need to
use the “?” as part of your command string then you would need to use the @replacechar
parameter to specify a different replacement character. Here is another
example that builds on the above example and uses the “{” as the replacement
character:


create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = ‘insert into #rowcount select
”Is the rowcount for table {?”,
count(*) from {’,
@replacechar = ‘{’,
@whereand = ‘and name like ”p%”’
select tablename as question, rowcnt from #rowcount
order by tablename
drop table #rowcount

Here is the output from this
command on my machine:


question rowcnt
———————————————— ———–
Is the rowcount for table [dbo].[pub_info]? 8
Is the rowcount for table [dbo].[publishers]? 8

There are two more
parameters to discuss, @precommand, and @postcommand. Here is an example that
uses both of these commands:


exec sp_MSforeachtable
@command1 = ‘print ”Processing table ?”’,
@whereand = ‘and name like ”p%”’,
@precommand = ‘Print ”precommand execution ” ‘,
@postcommand = ‘Print ”postcommand execution ” ‘

Here is the output from this
command when run on my machine:


precommand execution
Processing table [dbo].[pub_info]
Processing table [dbo].[publishers]
postcommand execution

As you can see, the “PRINT”
T-SQL command associated with the “@precommand” parameter was only executed
once, prior to processing through the tables. Whereas, the “@postcommmand”
statement was executed after all the tables where processed, and was only
executed once. Using the pre and post parameters would be useful if I had
some processing I wanted done prior to running a command against each table, and/or I needed to do some logic after all tables where processed.

sp_MSforeachdb

The SP “sp_MSforeachdb” is
found in the “master” database. This SP is used to execute a single T-SQL
statement, like “DBCC CHECKDB” or a number of T-SQL statements against every
database defined to a SQL Server instance. Here is the syntax for calling this
undocumented SP:

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

Where:

  • @RETURN_VALUE – is the return
    value which will be set by “sp_MSforeachdb”

  • @command1 – is the first command
    to be executed by “sp_MSforeachdb” and is defined as nvarchar(2000)

  • @replacechar – is a character in
    the command string that will be replaced with the table name being processed
    (default replacechar is a “?”)

  • @command2 and @command3 are two
    additional commands that can be run against each database

  • @precommand – is a nvarchar(2000)
    parameter that specifies a command to be run prior to processing any database

  • @postcommand – is also an
    nvarchar(2000) field used to identify a command to be run after all commands
    have been processed against all databases.

The parameters for sp_MSforeachdb
are very similar to sp_MSforeachtable. Therefore, there is no need to go over some
of the parameters since they provide the same functionality as I described in
the sp_MSforeachtable section above.

To show you how the
“sp_MSforeachdb” SP works let me go through a fairly simple example. This
example will perform a database backup, then a “DBCC CHECKDB” against each
database. Here is the code to perform this:


declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 =
‘if ”?” <> ”tempdb” print ”*** Processing DB ? ***”’
set @cmd2 = ‘if ”?” <> ”tempdb” backup database ? to disk=”c:temp?.bak”’
set @cmd3 = ‘if ”?” <> ”tempdb” dbcc checkdb(?)’
exec sp_MSforeachdb @[email protected],
@[email protected],
@[email protected]

Here you can see that I am really
processing three different commands. The first command is just a “PRINT”
statement so you can easily review the output by database. Remember how
“sp_MSforeachtable” SP had a parameter where you could constrain what
tables where processed, there is no parameter that provides this type of functionality
in the “sp_MSforeachdb” SP. Since SQL Server does not allow the “tempdb”
database to be backed up I needed a way to skip that database. This is why I
have used the “IF” statement in each of the commands I processed. The second
command (@command2) processes the databases backup, and the last command
(@command3) will run a “DBCC CHECKDB” command against all
databases except the “tempdb” database.

Comments on Using Undocumented SQL Server Stored Procedures

Some level of testing and
care should be taken when using undocumented code from Microsoft. Since these
SP’s are not documented, it means that Microsoft might change this code with any new
release or patch without notifying customers. Because of this, you need to
thoroughly test any code you write that uses these undocumented SPs against all new
releases of SQL Server. This testing should verify that your code still
functions as it did in old releases.

Conclusion

As you can see, these
undocumented Stored Procedures are much easier to use than using a CURSOR. Next
time you need to iteratively run the same code against all
tables, or all databases consider using these undocumented Stored Procedures.
Although, remember these Stored Procedures are undocumented and therefore
Microsoft may change their functionality at anytime.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles