문서화되지 않은 프로시저

 sp_MSforeachtable

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, @percommand, 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 eachnly executed once. clause and 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.

이 글은 스프링노트에서 작성되었습니다.