都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

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

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

f:id:koogucc11:20140611010607p:plain

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

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

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

exec sp_helptext sp_MSforeachtable

f:id:koogucc11:20140614125447p:plain

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 '件数取得終了'"

f:id:koogucc11:20140614153630p:plain

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

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