如题
---------------------------------------------------------------
用sp_who可以看到用户的状态,还有一个
syslocks 表
---------------------------------------------------------------
sp_lock可以看锁的情况
---------------------------------------------------------------
syslocks是6.X的系统表
syslockinfo 7.0 和2000的系统表。
这是我写的一个存储过程，查询存储过程所在的数据库的对象的锁住情况，适用于ms sql server7.0 和 ms sql server 2000。
CREATE PROCEDURE dbo.usp_lock AS
set nocount on
select p.hostname,
l.req_spid as spid,
l.rsc_objid,
left(v.name,4) As Type,
left(u.name,8) As Mode,
left(x.name,6) As lockStatus,
p.program_name,
p.loginame,
p.status as status,
p.blocked
into #locks
from master.dbo.syslockinfo l
join (select name,number from master.dbo.spt_values v where type = 'LR') v on l.rsc_type = v.number
join (select name,number from master.dbo.spt_values v where type = 'L') u on l.req_mode + 1 = u.number
join (select name,number from master.dbo.spt_values v where type = 'LS') x on l.req_status = x.number
join master.dbo.sysprocesses p on l.req_spid=p.spid
where l.rsc_dbid=db_id()
and l.rsc_objid>0
select l.hostname,
l.spid,
isnull(o.name,' ') as objname,
l.Type,
l.Mode,
l.lockStatus,
l.program_name,
l.loginame,
l.status,
l.blocked,
isnull(p.hostname,' ') as blockhostname,
isnull(p.program_name,' ') as blockprogram
from #locks l
join sysobjects o on l.rsc_objid=o.id
left join master.dbo.sysprocesses p on l.blocked=p.spid
order by o.name,l.hostname
drop table #locks
GO