Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 30, 2004

SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

By Gregory A. Larsen

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 @command1=@cmd1, 
                    @command2=@cmd2,
                    @command3=@cmd3

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date