Undocumented 저장 프로시저 : sp_MSforeachtable, sp_MSforeachdb

SQL 온라인 도움말에는 없는 유용한 두 가지 저장프로시저를 소개합니다.

sp_MSforeachtable
저장프로시저 이름에서 느껴지듯이 현 DB의 모든 사용자 테이블로 지정한 명령을 처리합니다.
사용법은 아래와 같습니다.

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

• @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

사용자 테이블에 대한 반복 작업을 처리할 경우 유용합니다.
예를 들면, 특정 스키마의 테이블을 다른 스키마로 개체를 이동하는 예제입니다.

sp_MSforeachtable @command1 = 'ALTER SCHEMA NewSchema TRANSFER ?'

다음 예는, p로 시작하는 테이블 목록을 표시하는 예제입니다.

EXEC sp_MSforeachtable 
@command1 = 'print ''Processing table ?''', 
@whereand = 'and name like ''p%''',
@precommand = 'Print ''precommand execution '' ',
@postcommand = 'Print ''postcommand execution '' '

실행하면 결과는 다음과 같습니다.

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

sp_MSforeachdb
저장프로시저 이름에서 느껴지듯이 현 SQL Server의 모든 DB 이름으로 지정한 명령을 처리합니다.
사용법은 아래와 같습니다.

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

• @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.

다음 예는 tempdb를 제외한 모든 DB를 백업하는 예제입니다.

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

참고사이트 : http://www.databasejournal.com/features/mssql/article.php/3441031