查看执行的sql产生了哪些锁

-`9NF:kB(`X Z0查看DBname数据除了共享锁之外的，所有锁，及当前执行是那个sql语句51Testing软件测试网7V"zcuf

f _5O+q5Q0use master
?0F}Y D|i,v.^0if Exists(select * from sys.procedures where name='P_test')

fVoDtM3?0drop proc P_test
s.x0H&W @B0go51Testing软件测试网m8n,k UB
Create proc P_test51Testing软件测试网y9{?#g6S y,Ms
@spid int
R2W$u-C(A m0as51Testing软件测试网/P,Jqu/t
x-~5|
dbcc inputbuffer(@spid)
g o$lde4P8}0go51Testing软件测试网v;ep?$B
 51Testing软件测试网
u|'e| ek ~
^
 Create table #LockInfo(51Testing软件测试网EoEZBS x
 EventType nvarchar(4000),51Testing软件测试网:U6`Le_e&U:{9Mw
 [Parameters] int,
|u(`8|z-gX/C0  EventInfo nvarchar(4000),51Testing软件测试网Z$Z)R]4}v:R
  );

 CREATE Table #Lock(spid int,

aheo ~^`g0     dpid int,51Testing软件测试网G)M8i?R2P1RG/h
     objid int,51Testing软件测试网1y eL gX,E
     indld int,51Testing软件测试网cW7g"\$vyg
     [Type] nvarchar(20),51Testing软件测试网Ty*?-h'x
     Resource nvarchar(50),
EBH Y4P|'[G`0     Mode nvarchar(10),
~6iaBMc'A-WA7H0     Status nvarchar(10)51Testing软件测试网*rT#f)?Q[
 );
)C \%Aodj8}g0 
zM znY#@0   51Testing软件测试网 KF,}o#iZ0G^e$h
 INSERT INTO #Lock51Testing软件测试网ii2}@D
     EXEC sp_lock 
6EN&i(mE@0    
T:@+aA!D&b,~0 51Testing软件测试网/O-Km0?6SA
 declare @shardid int
&q!f8?6wu0]D!n:@0 select @shardid=dbid from sysdatabases where name='DBname'
X]a+@6lP~0 51Testing软件测试网f?Y8z:n#CK)as
 delete from #Lock where dpid<>@shardid or Mode='S'51Testing软件测试网jOT$N#o/ct

51Testing软件测试网^-[te)rQ6mC
 DECLARE crsr Cursor FOR   
BI~k$K%ss0 select spid from #lock where Mode<>'S';  51Testing软件测试网eZ%F4L)oJW*F
 

oX~| VPN2B0 declare @PreccessID int;
.E3}+Z-ha4t&Bc0 open crsr;
Y%w
rry`N!x0 Fetch next from crsr into @PreccessID;

 WHILE (@@FETCH_STATUS = 0)51Testing软件测试网:mNW0}y9EW
 BEGIN
'p9\g"Lj(?t0 51Testing软件测试网k Ag+ItW5|#Uf
  insert into #LockInfo exec P_test @PreccessID;51Testing软件测试网"q)if\ z9Gw5U
  --dbcc inputbuffer(@PreccessID)51Testing软件测试网zx9qU
v(?q
  FETCH NEXT FROM crsr INTO @PreccessID;51Testing软件测试网$?C@Cl#Yk?
 END51Testing软件测试网wI%QG#I8DV
 close crsr;
.z4iZ0h*FZ6J0 
P'MA;t'eT0 select #LockInfo.*,#Lock.Mode,#Lock.spid as Processid from #LockInfo, #lock51Testing软件测试网?#m|DWoS`#l7Y
 drop table #LockInfo;51Testing软件测试网$?{@R"T
 DROP Table #Lock;51Testing软件测试网/kw5G@+ia Q
 51Testing软件测试网
d[w5t8m2I Ch
 DEALLOCATE crsr;

4L
i}[D"{0go

2fiF ^Af
ISc0