undocumented なものを見つけたので、調べてみる - その1 ( sp_MSforeachtable ) -

　特集シリーズをやろうと思って考えたのですが、undocumented 系をやってみようと思います。SQL Server の機能には、ドキュメントのないものも多く存在します。それらを取り上げていきます。まず第一回は、sp_MSforeachtable です。for each table といった名前から推測するに、テーブルをぐるぐる回す処理なのでしょう (適当www) 

　よくわからないので、早速試してみましょう。SQL Server Management Studio で見ると、下図のような感じです。

　引数の名前から想像するに、下記に推定してみました。

• @command1
  コマンドなので、何かしらの命令をかけるんでしょう。
• @replacechar
  文字を置き換え？んー。
• @command2
  コマンドなので、何かしらの命令をかけるんでしょう。
• @command3
  コマンドなので、何かしらの命令をかけるんでしょう。
• @whereand
  where句の条件とかかける？この場合は、テーブルを絞り込む条件を書く？
• @precommand
  @comand1,2,3のコマンドの前に実行するものでしょう。
• @postcommand
  @comand1,2,3のコマンド実行後に何か実行するものを指定するのでしょう。

　推測しても仕方ないので、下記のストアドを実行し、ストアドの中身を参照してみましょう。sp_helptext を使用し( 記事の内容ここからでもよかったですねww )

exec sp_helptext sp_MSforeachtable

create proc sys.sp_MSforeachtable
 @command1 nvarchar(2000),
 @replacechar nchar(1) = N'?', 
 @command2 nvarchar(2000) = null,
 @command3 nvarchar(2000) = null, 
 @whereand nvarchar(2000) = null,
 @precommand nvarchar(2000) = null,
 @postcommand nvarchar(2000) = null
as
 declare @mscat nvarchar(12)
 select @mscat = ltrim(str(convert(int, 0x0002)))

 if (@precommand is not null)
 exec(@precommand)

 exec(N'declare hCForEachTable cursor global for select ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join sys.all_objects syso on o.id =
 syso.object_id '
 + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
 + @whereand)
 declare @retval int
 select @retval = @@error
 if (@retval = 0)
 exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

 if (@retval = 0 and @postcommand is not null)
 exec(@postcommand)

 return @retval

• @command1
  指定されたステートメントを実行します。詳細は、sp_MSforeach_worker をみないといけないですね。MSforeach の worker なので、MSforeachTable からの使用が前提のようです。
• @replacechar
  詳細は、sp_MSforeach_worker をみないといけないですね。
• @command2
  指定されたステートメントを実行します。詳細は、sp_MSforeach_worker をみないといけないですね。
• @command3
  指定されたステートメントを実行します。詳細は、sp_MSforeach_worker をみないといけないですね。
• @whereand
  where句の条件を記述することができます。o.name などてテーブル名等を絞り込むことが可能です。
• @precommand
  @comand1,2,3のコマンドの前に実行されるステートメントを指定します。
• @postcommand
  @comand1,2,3のコマンド実行後に実行されるステートメントを指定します。

　例えば以下のような処理書けたりします。( AdventureWorks を使っています。 )

exec sp_MSforeachtable @command1="print '?' select count(*) from ?",
                       @whereand = "and o.name like '%State%'",
                       @precommand="print '件数' print ''",
                       @postcommand="print '' print '件数取得終了'"

※あ、日本語化けてますね。

　undocumented なものの中には、つかえそうなものも多いです。今回のものは、統計情報の更新とか、インデックスのリビルド処理等など色々なものに応用できそうです。あと、sp_MSforeachtable の中で使用されている sp_MSforeach_worker も気になりますね。次回は、replacechar、command2、command3 も使用してみます。