SQLServerでロックしているSQL文をまとめて表示したい

Question

SQLServer Management Studioの利用状況モニタのプロセス画面で、
ロックしている行を選択し、右クリックで詳細を選ぶとそのSQL文が参照できます。

ですが、大量にロックが出ている場合や、緊急時などに一つずつ開いて確認できません。

そこでsp_lockでロックしている一覧が参照できるように、
SQL、プロシージャ等を利用してロックしているSQL文を一気に表示する方法はありますか？

Answer 1

tm1995さん、こんにちは。

私が調査に使っているクエリがあるので紹介しておきます。
お役にたつかどうかはわかりませんが…

--変数の定義
declare @dbname nvarchar(20)
declare @hostname nvarchar(20)
declare @sql nvarchar(max)

--データベース名、ホスト名の定義
set @dbname = 'db1'
set @hostname = 'server1'

--SQLServer内のプロセス状況をリストして、DBCCコマンド化するためにカーソルを宣言
DECLARE CURSOR01 CURSOR FOR
select 'DBCC INPUTBUFFER(' + convert(nvarchar(10),spid) + ')' as sql
from master.dbo.sysprocesses
where db_name(dbid) = @dbname
and hostname = @hostname
and spid > 50

--カーソルをオープン
OPEN CURSOR01

--対象行の取出し
FETCH NEXT FROM CURSOR01 INTO @sql

--対象行の処理
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @sql
FETCH NEXT FROM CURSOR01 INTO @sql
END

--カーソルのクローズ
CLOSE CURSOR01

--カーソルの宣言解除
DEALLOCATE CURSOR01

Answer 2

回答ありがとうございます。

上記クエリを実行したところ、確かにSQL文は出力したのですが、

SELECT * FROM  aaa WHERE bbb=@P0 というようになります。

ロックしている対象を明確にするため、パラメータ化された部分(@P0など)の値を見る方法はありますか？

Answer 3

tm1995さん、こんにちは。

クエリ内に含まれる変数に何が入っているか、ということですね
正直なところ、私もその手法がわかりません。

そのため、変数の内容を知りたい場合には、そのクエリ（ストアド）の処理直前で "PRINT @P0" を追加して変数内部を直接出力するように修正してデバッグしています。
あまり力になれず申し訳ありません。
もっと良い方法を知っている人が出てくることを祈っております。